Design Question: column storing a type

David Portas

"Zester" <> wrote in message
news:.gbl...
> Extra table would bring more joints, right? To use sql statement to find
> out user permissions, we would have to do this:
>
> Assume PrintPermission table is defined with 2 columns
> PrintPermission
> (
> PermissionType int primary key not null default( 0 ), check
> PermissionType in (0,1,2),
> PermissionDesc varchar(50) not null default ( 'NeverAllow' ), check in
> ('NeverAllow', 'AllowPrintPublicForms', 'AllowPrintAllForms' )
> )
>
> select u.UserName, printPerm.PermissionDesc
> from User u JOIN PrintPermission printPerm on u.PrintPermissionType =
> printPerm.PermissionType
>
>


Compared to what alternative? Either the description is in the database or
it isn't. If it isn't then it's irrelevant whether or not you create an
extra table. No join is necessary:

SELECT u.UserName, u.PrintPermissionType
FROM User;

If you DO want the description in the database then I don't know what
alternative you are proposing.

--
David Portas

Rick Sawtell

"Zester" <> wrote in message
news:uhu1$.gbl...
> Hi Rick,
>
> what's the benefit for this approach? FK must references a primary key, in
> your suggestion, the primary key in PermissionType is PermissionTypeID
> (not the possible value of the PrintPermissionType), so it's a lose
> relationship. I still don't see the benefit that worth the joining
> troubles. thanks!


My apologies.. In your primary tables, add the value in the IDENTITY column
from the PermissionType table as a FK.

Example:

CREATE dbo.SomeTableStoringData (
x int PRIMARY KEY,
y varchar(100), -- Some data
z varchar(100), -- Some data
PermissionTypeID int NOT NULL,

CONSTRAINT FK_SomeTableStoringData_PermissionType FOREIGN KEY
(PermissionTypeID) REFERENCES dbo.PermissionType (PermissionTypeID)
)


SELECT
SomeTableStoringData.x,
SomeTableStoringData.y,
PermissionType.Description,
PermissionType.PermissionTypeEnum
FROM
dbo.SomeTableStoringData
JOIN
dbo.PermissionType
ON SomeTableStoringData.PermissionTypeID = PermissionType.PermissionTypeID
-- You can include a WHERE clause to ensure that the correct table
permissions are being looked at. Example:
WHERE PermissionType.PermissionTypeEnum = 2 -- In this example, the enum 2
means ViewAll


Rick Sawtell

Rick Sawtell

"Zester" <> wrote in message
news:uhu1$.gbl...
> Hi Rick,
>
> what's the benefit for this approach? FK must references a primary key, in
> your suggestion, the primary key in PermissionType is PermissionTypeID
> (not the possible value of the PrintPermissionType), so it's a lose
> relationship. I still don't see the benefit that worth the joining
> troubles. thanks!


My apologies.. In your primary tables, add the value in the IDENTITY column
from the PermissionType table as a FK.

Example:

CREATE dbo.SomeTableStoringData (
x int PRIMARY KEY,
y varchar(100), -- Some data
z varchar(100), -- Some data
PermissionTypeID int NOT NULL,

CONSTRAINT FK_SomeTableStoringData_PermissionType FOREIGN KEY
(PermissionTypeID) REFERENCES dbo.PermissionType (PermissionTypeID)
)


SELECT
SomeTableStoringData.x,
SomeTableStoringData.y,
PermissionType.Description,
PermissionType.PermissionTypeEnum
FROM
dbo.SomeTableStoringData
JOIN
dbo.PermissionType
ON SomeTableStoringData.PermissionTypeID = PermissionType.PermissionTypeID
-- You can include a WHERE clause to ensure that the correct table
permissions are being looked at. Example:
WHERE PermissionType.PermissionTypeEnum = 2 -- In this example, the enum 2
means ViewAll


Rick Sawtell

Zester

Comparing with option (1); the other options including what you brought
updon't have enough benefits to offset the joining troubles. Of course,
someone outthere might have a few more pros to add to them that can tip the
scale.

Option (1) gives me the descriptions in the db with no joint. I am basically
seeking out strong arguments against it being the best approach (when no
other table would share the type).

"David Portas" <> wrote in message
news:.gbl...
> "Zester" <> wrote in message
> news:.gbl...
>> Extra table would bring more joints, right? To use sql statement to find
>> out user permissions, we would have to do this:
>>
>> Assume PrintPermission table is defined with 2 columns
>> PrintPermission
>> (
>> PermissionType int primary key not null default( 0 ), check
>> PermissionType in (0,1,2),
>> PermissionDesc varchar(50) not null default ( 'NeverAllow' ), check in
>> ('NeverAllow', 'AllowPrintPublicForms', 'AllowPrintAllForms' )
>> )
>>
>> select u.UserName, printPerm.PermissionDesc
>> from User u JOIN PrintPermission printPerm on u.PrintPermissionType =
>> printPerm.PermissionType
>>
>>
>
>
> Compared to what alternative? Either the description is in the database or
> it isn't. If it isn't then it's irrelevant whether or not you create an
> extra table. No join is necessary:
>
> SELECT u.UserName, u.PrintPermissionType
> FROM User;
>
> If you DO want the description in the database then I don't know what
> alternative you are proposing.
>
> --
> David Portas
>
>

Zester

Comparing with option (1); the other options including what you brought
updon't have enough benefits to offset the joining troubles. Of course,
someone outthere might have a few more pros to add to them that can tip the
scale.

Option (1) gives me the descriptions in the db with no joint. I am basically
seeking out strong arguments against it being the best approach (when no
other table would share the type).

"David Portas" <> wrote in message
news:.gbl...
> "Zester" <> wrote in message
> news:.gbl...
>> Extra table would bring more joints, right? To use sql statement to find
>> out user permissions, we would have to do this:
>>
>> Assume PrintPermission table is defined with 2 columns
>> PrintPermission
>> (
>> PermissionType int primary key not null default( 0 ), check
>> PermissionType in (0,1,2),
>> PermissionDesc varchar(50) not null default ( 'NeverAllow' ), check in
>> ('NeverAllow', 'AllowPrintPublicForms', 'AllowPrintAllForms' )
>> )
>>
>> select u.UserName, printPerm.PermissionDesc
>> from User u JOIN PrintPermission printPerm on u.PrintPermissionType =
>> printPerm.PermissionType
>>
>>
>
>
> Compared to what alternative? Either the description is in the database or
> it isn't. If it isn't then it's irrelevant whether or not you create an
> extra table. No join is necessary:
>
> SELECT u.UserName, u.PrintPermissionType
> FROM User;
>
> If you DO want the description in the database then I don't know what
> alternative you are proposing.
>
> --
> David Portas
>
>

Zester

So basically, this approach would unify all possible values of all
permission types in the system, right? PermissionTypeID can be 125 when 125
is defined as AllowPrintingPrivateForms. What about if we need to store
another permission for viewing files. That would result in another
column PermissionTypeID2. If a user in our system has 50 permission types,
it would be PermissionTypeID1....50?

Now the need for joining is even higher, right? that is because I can't just
count on my memory as much that PermissionType = 0 means no permission (the
basic default situation) since it would have a value of 124. What's the
benefit? thanks!

"Rick Sawtell" <> wrote in message
news:.gbl...
>
> "Zester" <> wrote in message
> news:uhu1$.gbl...
>> Hi Rick,
>>
>> what's the benefit for this approach? FK must references a primary key,
>> in your suggestion, the primary key in PermissionType is PermissionTypeID
>> (not the possible value of the PrintPermissionType), so it's a lose
>> relationship. I still don't see the benefit that worth the joining
>> troubles. thanks!
>
>
> My apologies.. In your primary tables, add the value in the IDENTITY
> column from the PermissionType table as a FK.
>
> Example:
>
> CREATE dbo.SomeTableStoringData (
> x int PRIMARY KEY,
> y varchar(100), -- Some data
> z varchar(100), -- Some data
> PermissionTypeID int NOT NULL,
>
> CONSTRAINT FK_SomeTableStoringData_PermissionType FOREIGN KEY
> (PermissionTypeID) REFERENCES dbo.PermissionType (PermissionTypeID)
> )
>
>
> SELECT
> SomeTableStoringData.x,
> SomeTableStoringData.y,
> PermissionType.Description,
> PermissionType.PermissionTypeEnum
> FROM
> dbo.SomeTableStoringData
> JOIN
> dbo.PermissionType
> ON SomeTableStoringData.PermissionTypeID =
> PermissionType.PermissionTypeID
> -- You can include a WHERE clause to ensure that the correct table
> permissions are being looked at. Example:
> WHERE PermissionType.PermissionTypeEnum = 2 -- In this example, the enum
> 2 means ViewAll
>
>
> Rick Sawtell
>
>
>
>
>
>
>
>

Zester

So basically, this approach would unify all possible values of all
permission types in the system, right? PermissionTypeID can be 125 when 125
is defined as AllowPrintingPrivateForms. What about if we need to store
another permission for viewing files. That would result in another
column PermissionTypeID2. If a user in our system has 50 permission types,
it would be PermissionTypeID1....50?

Now the need for joining is even higher, right? that is because I can't just
count on my memory as much that PermissionType = 0 means no permission (the
basic default situation) since it would have a value of 124. What's the
benefit? thanks!

"Rick Sawtell" <> wrote in message
news:.gbl...
>
> "Zester" <> wrote in message
> news:uhu1$.gbl...
>> Hi Rick,
>>
>> what's the benefit for this approach? FK must references a primary key,
>> in your suggestion, the primary key in PermissionType is PermissionTypeID
>> (not the possible value of the PrintPermissionType), so it's a lose
>> relationship. I still don't see the benefit that worth the joining
>> troubles. thanks!
>
>
> My apologies.. In your primary tables, add the value in the IDENTITY
> column from the PermissionType table as a FK.
>
> Example:
>
> CREATE dbo.SomeTableStoringData (
> x int PRIMARY KEY,
> y varchar(100), -- Some data
> z varchar(100), -- Some data
> PermissionTypeID int NOT NULL,
>
> CONSTRAINT FK_SomeTableStoringData_PermissionType FOREIGN KEY
> (PermissionTypeID) REFERENCES dbo.PermissionType (PermissionTypeID)
> )
>
>
> SELECT
> SomeTableStoringData.x,
> SomeTableStoringData.y,
> PermissionType.Description,
> PermissionType.PermissionTypeEnum
> FROM
> dbo.SomeTableStoringData
> JOIN
> dbo.PermissionType
> ON SomeTableStoringData.PermissionTypeID =
> PermissionType.PermissionTypeID
> -- You can include a WHERE clause to ensure that the correct table
> permissions are being looked at. Example:
> WHERE PermissionType.PermissionTypeEnum = 2 -- In this example, the enum
> 2 means ViewAll
>
>
> Rick Sawtell
>
>
>
>
>
>
>
>

David Portas

"Zester" <> wrote in message
news:.gbl...
> Comparing with option (1); the other options including what you brought
> updon't have enough benefits to offset the joining troubles. Of course,
> someone outthere might have a few more pros to add to them that can tip
> the scale.
>
> Option (1) gives me the descriptions in the db with no joint. I am
> basically seeking out strong arguments against it being the best approach
> (when no other table would share the type).
>

A disadvantage of (1) is that it needs a schema change to add a new type.
The advantage of (3) is that it doesn't and it *doesn't* require any extra
joins either compared to (1). But I think I'm just failing to communicate
that second point so it's over to you from here on...

--
David Portas

David Portas

"Zester" <> wrote in message
news:.gbl...
> Comparing with option (1); the other options including what you brought
> updon't have enough benefits to offset the joining troubles. Of course,
> someone outthere might have a few more pros to add to them that can tip
> the scale.
>
> Option (1) gives me the descriptions in the db with no joint. I am
> basically seeking out strong arguments against it being the best approach
> (when no other table would share the type).
>

A disadvantage of (1) is that it needs a schema change to add a new type.
The advantage of (3) is that it doesn't and it *doesn't* require any extra
joins either compared to (1). But I think I'm just failing to communicate
that second point so it's over to you from here on...

--
David Portas

Zester

so with 50 possible permission types in a system, you would have 50 of these
tables with 2 columns each?

"David Portas" <> wrote in message
news:%.gbl...
> "Zester" <> wrote in message
> news:.gbl...
>> Comparing with option (1); the other options including what you brought
>> updon't have enough benefits to offset the joining troubles. Of course,
>> someone outthere might have a few more pros to add to them that can tip
>> the scale.
>>
>> Option (1) gives me the descriptions in the db with no joint. I am
>> basically seeking out strong arguments against it being the best approach
>> (when no other table would share the type).
>>
>
> A disadvantage of (1) is that it needs a schema change to add a new type.
> The advantage of (3) is that it doesn't and it *doesn't* require any extra
> joins either compared to (1). But I think I'm just failing to communicate
> that second point so it's over to you from here on...
>
> --
> David Portas
>
>