Design Question: column storing a type

Zester

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!!

David Portas

"Zester" <> wrote in message
news:.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

Zester

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" <> wrote in message
news:.gbl...
> "Zester" <> wrote in message
> news:.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
>
>

Rick Sawtell

"Zester" <> wrote in message
news:.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

David Portas

"Zester" <> wrote in message
news:.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

Zester

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" <> wrote in message
news:.gbl...
>
> "Zester" <> wrote in message
> news:.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
>
>
>
>

Rick Sawtell

"Zester" <> wrote in message
news:evdwX$.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

Zester

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" <> wrote in message
news:.gbl...
> "Zester" <> wrote in message
> news:.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
>
>

Zester

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" <> wrote in message
news:.gbl...
>
> "Zester" <> wrote in message
> news:evdwX$.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
>
>
>
>
>

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