July 18, 2006 at 9:17 am
I have a DB with multiple customer types, currently using three. I have the queries and SP's set up to do processes based on which type; Manuf, Supplier or DBOwner. I need to add other types and some overlap. My simple hierachy is not going to work without continually adding columns for different types and changing all of the queries and SP's. Should I continue doing this in nested queries or would a field with a bit mask (each bit position representing access rights) be more flexible and easier to maintain.
July 18, 2006 at 9:38 am
technically you should normalise and create a lookup table to join against .. vs ... your choice to method is to which is least likely to break your application(s) , which is the most scalable and which is safest/simplest to implement.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 18, 2006 at 11:58 am
Here is what i have with some of the select stuff removed for brevity. I have tables relating Manuf to Parts, Suppliers to Data and Data being associated with Parts through another table. If I add a Middleman, then each of these has to have another lower level nest to limit the select statements and if another requirement is put on me, then I'm afraid this will just get worse. The Filter is the UserName associated with who they are and therefore what they have access to in each of the tables. I don't see any other way to do it.
CREATE PROCEDURE spViewMainData
(@username nvarchar(10))
AS
-- Test if the Manufacturer listed is DBOwner, then send all results
IF EXISTS (Select Manufacturer FROM tblSUserData WHERE Manufacturer = 'DBOwner' AND UserName = @username)
Begin
SELECT "ALL DATA"
FROM tblSMainData INNER JOIN tblSSuppliers ON tblSMainData.Suppl = tblSSuppliers.LOGO ORDER BY tblSMainData.Data
End
-- Test if Manufacturer entry is NOTMAN, then send Supplier based on Parameter
IF EXISTS (Select Manufacturer FROM tblSUserData WHERE Manufacturer = 'NOTMAN' AND UserName = @username)
Begin
SELECT DISTINCT "Limited Data"
FROM tblSMainData INNER JOIN
tblSSuppliers ON tblSMainData.Suppl = tblSSuppliers.LOGO CROSS JOIN
tblSUserData
WHERE (tblSMainData.Suppl LIKE tblSUserData.Supplier + N'%') AND (tblSUserData.UserName = @username)
ORDER BY tblSMainData.Data
End
Else
-- Send Manufacturer Data based on Parameter
SELECT DISTINCT "Limited Data"
FROM tblSMianData INNER JOIN
tblSMainPart ON tblSMainData.Data = tblSMainPart.Data INNER JOIN
tblSPartsData ON tblSMainPart.PartData = tblSPartsData.PartNum INNER JOIN
tblSManuf ON tblSPartsData.Manufacturer = tblSManuf.Manufacturer INNER JOIN
tblSUserData ON tblSManuf.Manufacturer = tblSUserData.Manufacturer INNER JOIN
tblSSuppliers ON tblSMainData.Suppl = tblSSuppliers.LOGO
WHERE ( tblSPartsData.Manufacturer LIKE tblSUserData.Manufacturer + N'%') AND (tblSUserData.UserName = @username)
ORDER BY tblSMainData.Data
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply