Design Question: column storing a type

Hi,
I'm 50/50 about this design topic, could someone please shed some light?
thanks!
I often have to add a column to our db just to store a permission type of a
user etc... In the code (C#), it should be enum type to UI level; in the
db, I am not sure which of these 2 ways is "generally" a better design:
1) varchar type with check constraint to make sure certain type description
can be stored.
2) int type with or without check constraint (without would allow the code
to extend the enum type without changing db)
I know that (2) is a bit faster and take less space but it takes a long time
to look up some info (imagine if we have 50 of these types through out the
system). (1) would give us a better context by run a sql statement, it would
be harder to make reading mistake and bug in stored proc because it's highly
descriptive.
Thanks!!
"Zester" <zeze@.nottospam.com> wrote in message
news:O5KWfW1OIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm 50/50 about this design topic, could someone please shed some light?
> thanks!
> I often have to add a column to our db just to store a permission type of
> a user etc... In the code (C#), it should be enum type to UI level; in
> the db, I am not sure which of these 2 ways is "generally" a better
> design:
> 1) varchar type with check constraint to make sure certain type
> description can be stored.
> 2) int type with or without check constraint (without would allow the code
> to extend the enum type without changing db)
> I know that (2) is a bit faster and take less space but it takes a long
> time to look up some info (imagine if we have 50 of these types through
> out the system). (1) would give us a better context by run a sql
> statement, it would be harder to make reading mistake and bug in stored
> proc because it's highly descriptive.
> Thanks!!
>
Or
3) A column with a FOREIGN KEY referencing a PermissionType table.
If you are likely to modify the set of types frequently then go for 3)
because that way you can easily use the PermissionType table to drive the
options available in your app without any code change.
If you are happy to make schema and code changes whenever the set of types
changes then use 1).
Not certain what your intention is with 2). I think you mean a surrogate
key, which is a differrent question altogether and one that doesn't have a
simple answer. I suggest you consult your DBA / Database Architect.
David Portas
|||For (2), I meant that we just stored the enum value in the form of the
integer without referencing to the definition table (which is option 3 you
pointed out). For example, in C/C# code
enum AllowPrintPermission
{
None, // never allow = 0
AllowPrintPublicForms, // = 1
AllowPrintAllForms // = 2
}
This type will be stored as 0,1,2 respectively
(3) would result in many tables for us. These types are not shared by
multiple tables.
We have lots of user permissions, it's a con to do many joints to get the
meaning, sometimes the sql statement can get so complex that data mining and
debugging tasks down the road can be a high cost.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uJuYZe1OIHA.4476@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:O5KWfW1OIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Or
> 3) A column with a FOREIGN KEY referencing a PermissionType table.
> If you are likely to modify the set of types frequently then go for 3)
> because that way you can easily use the PermissionType table to drive the
> options available in your app without any code change.
> If you are happy to make schema and code changes whenever the set of types
> changes then use 1).
> Not certain what your intention is with 2). I think you mean a surrogate
> key, which is a differrent question altogether and one that doesn't have a
> simple answer. I suggest you consult your DBA / Database Architect.
> --
> David Portas
>
|||"Zester" <zeze@.nottospam.com> wrote in message
news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
> For (2), I meant that we just stored the enum value in the form of the
> integer without referencing to the definition table (which is option 3 you
> pointed out). For example, in C/C# code
> enum AllowPrintPermission
> {
> None, // never allow = 0
> AllowPrintPublicForms, // = 1
> AllowPrintAllForms // = 2
> }
> This type will be stored as 0,1,2 respectively
> (3) would result in many tables for us. These types are not shared by
> multiple tables.
> We have lots of user permissions, it's a con to do many joints to get the
> meaning, sometimes the sql statement can get so complex that data mining
> and debugging tasks down the road can be a high cost.
>
If you don't mind breaking normalization rules a bit, then you can do a
modified version of 3.
In the lookup table, include an identifier of some type (TableName for
example).
Then a single PermissionType table could support many tables in your db.
Example:
CREATE TABLE dbo.PermissionType (
PermissionTypeID int IDENTITY(1,1) NOT NULL PRIMARY KEY
SchemaName sysname NOT NULL,
TableName sysname NOT NULL,
PermissionType varchar(100) NOT NULL,
PermissionTypeEnum int NOT NULL)
ALTER TABLE dbo.PermissionType ADD UNIQUE CONSTRAINT UC_PermissionType
(SchemaName, TableName, PermissionType, PermissionTypeEnum)
INSERT PermissionType VALUES ('dbo.', 'Payroll', 'ViewAll', 0)
Rick Sawtell
MCT, MCSD, MCDBA
|||"Zester" <zeze@.nottospam.com> wrote in message
news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
> For (2), I meant that we just stored the enum value in the form of the
> integer without referencing to the definition table (which is option 3 you
> pointed out). For example, in C/C# code
> enum AllowPrintPermission
> {
> None, // never allow = 0
> AllowPrintPublicForms, // = 1
> AllowPrintAllForms // = 2
> }
> This type will be stored as 0,1,2 respectively
> (3) would result in many tables for us. These types are not shared by
> multiple tables.
> We have lots of user permissions, it's a con to do many joints to get the
> meaning, sometimes the sql statement can get so complex that data mining
> and debugging tasks down the road can be a high cost.
>
Creating an extra table does not mean you need any more joins or more
complex SQL than before. Use exactly the same queries you would in your
other solutions. Creating an extra table may just make it easier to maintain
the set of values. "Many tables" should not pose any kind of problem that I
can see.
Of course there is no single "right" answer. Just my 0.02
David Portas
|||Thanks for pointing to a new direction; so what type would the column in
payroll table be? int? how does it reference (via foreign key) to the
Permission table when the value is not a primary key in Permission table?
If there is no connection via foreign key to maintain the integrity of the
relationship, I don't see the benefit of this approach. Could you please
explain? thanks!!
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> wrote in message
news:OleRx31OIHA.5524@.TK2MSFTNGP05.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
> If you don't mind breaking normalization rules a bit, then you can do a
> modified version of 3.
> In the lookup table, include an identifier of some type (TableName for
> example).
> Then a single PermissionType table could support many tables in your db.
> Example:
> CREATE TABLE dbo.PermissionType (
> PermissionTypeID int IDENTITY(1,1) NOT NULL PRIMARY KEY
> SchemaName sysname NOT NULL,
> TableName sysname NOT NULL,
> PermissionType varchar(100) NOT NULL,
> PermissionTypeEnum int NOT NULL)
> ALTER TABLE dbo.PermissionType ADD UNIQUE CONSTRAINT UC_PermissionType
> (SchemaName, TableName, PermissionType, PermissionTypeEnum)
>
> INSERT PermissionType VALUES ('dbo.', 'Payroll', 'ViewAll', 0)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||"Zester" <zeze@.nottospam.com> wrote in message
news:evdwX$1OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Thanks for pointing to a new direction; so what type would the column in
> payroll table be? int? how does it reference (via foreign key) to the
> Permission table when the value is not a primary key in Permission table?
> If there is no connection via foreign key to maintain the integrity of the
> relationship, I don't see the benefit of this approach. Could you please
> explain? thanks!!
1. Use the IDENTITY column in the base tables. You do not have to create a
FK constraint, but it is not a bad idea.
2. Perform joins on that IDENTITY column to get your enum
Rick
|||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
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O1igq91OIHA.4912@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
> Creating an extra table does not mean you need any more joins or more
> complex SQL than before. Use exactly the same queries you would in your
> other solutions. Creating an extra table may just make it easier to
> maintain the set of values. "Many tables" should not pose any kind of
> problem that I can see.
> Of course there is no single "right" answer. Just my 0.02
> --
> David Portas
>
|||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!
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> wrote in message
news:efRNMH2OIHA.5980@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:evdwX$1OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> 1. Use the IDENTITY column in the base tables. You do not have to create
> a FK constraint, but it is not a bad idea.
> 2. Perform joins on that IDENTITY column to get your enum
>
> Rick
>
>
>
|||"Zester" <zeze@.nottospam.com> wrote in message
news:uhu1$L2OIHA.5988@.TK2MSFTNGP02.phx.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