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...
>> 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" <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...
>> 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: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...
>> 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
>|||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...
>> 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" <zeze@.nottospam.com> wrote in message
news:eGke7G2OIHA.1164@.TK2MSFTNGP02.phx.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" <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|||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" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OhmeqN2OIHA.536@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:eGke7G2OIHA.1164@.TK2MSFTNGP02.phx.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
>|||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" <r_sawtell@.nospam.hotmail.com> wrote in message
news:OPD1vS2OIHA.4272@.TK2MSFTNGP06.phx.gbl...
> "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
>
>
>
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.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|||so with 50 possible permission types in a system, you would have 50 of these
tables with 2 columns each?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.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
>|||I totally agree with David here. These codes should each have their own
table with a foreign key constraint. You don't always have to use a
surrogate key for them either, use the name of the permission as the Key and
then you don't have to do any extra joins. If the name is too long you can
usually come up with a unique abbreviation that still conveys the meaning
and you still don't have to do the extra joins.
That said, designing a database around eliminating joins is the wrong
approach. These kind of "lookup" tables are usually pretty small so the
joins are very fast and efficient. Even if they aren't small, rather than
searching for ways to avoid the joins, you can use things such as indexed
views to speed up queries.
Don't compromise data integrity and long term viability for the sake of
saving a few joins.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.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
>|||Thanks for your input. Hm, that's true that we can just keeping using the
text description in the main table and just create another table to be
referenced. So you would have 50 extra tables, but why data integrity is an
issue when just use check constraint to make sure the set options are
declared and reinforced? The only drawback I see so far is if we need to add
new enum value to the set, we need to change the check constraint instead of
just simply inserting another entry in the permission type definition table.
However, to do the insertion, we need 50 UI pieces. I think there are
something I should point out, we host db solution internally (on-site,
web-based) so modifying the db constraints is an easy thing for us comparing
with releasing db schema with the solution like other product.
"DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
news:eN04BR3OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>I totally agree with David here. These codes should each have their own
>table with a foreign key constraint. You don't always have to use a
>surrogate key for them either, use the name of the permission as the Key
>and then you don't have to do any extra joins. If the name is too long you
>can usually come up with a unique abbreviation that still conveys the
>meaning and you still don't have to do the extra joins.
> That said, designing a database around eliminating joins is the wrong
> approach. These kind of "lookup" tables are usually pretty small so the
> joins are very fast and efficient. Even if they aren't small, rather than
> searching for ways to avoid the joins, you can use things such as indexed
> views to speed up queries.
> Don't compromise data integrity and long term viability for the sake of
> saving a few joins.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> "Zester" <zeze@.nottospam.com> wrote in message
>> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.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
>>
>|||Constraints work for this if the number of valid values is small and
relatively static. I still prefer the use of tables and FK's though. You
don't need to create 50 new UI pieces to update those tables, only those
that will change "frequently". I think it's easier to insert or update
tables as part of a deployment, than to change check constraints...
"Zester" <zeze@.nottospam.com> wrote in message
news:uIE29b3OIHA.3532@.TK2MSFTNGP04.phx.gbl...
> Thanks for your input. Hm, that's true that we can just keeping using the
> text description in the main table and just create another table to be
> referenced. So you would have 50 extra tables, but why data integrity is
> an issue when just use check constraint to make sure the set options are
> declared and reinforced? The only drawback I see so far is if we need to
> add new enum value to the set, we need to change the check constraint
> instead of just simply inserting another entry in the permission type
> definition table. However, to do the insertion, we need 50 UI pieces. I
> think there are something I should point out, we host db solution
> internally (on-site, web-based) so modifying the db constraints is an easy
> thing for us comparing with releasing db schema with the solution like
> other product.
> "DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
> news:eN04BR3OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>I totally agree with David here. These codes should each have their own
>>table with a foreign key constraint. You don't always have to use a
>>surrogate key for them either, use the name of the permission as the Key
>>and then you don't have to do any extra joins. If the name is too long
>>you can usually come up with a unique abbreviation that still conveys the
>>meaning and you still don't have to do the extra joins.
>> That said, designing a database around eliminating joins is the wrong
>> approach. These kind of "lookup" tables are usually pretty small so the
>> joins are very fast and efficient. Even if they aren't small, rather
>> than searching for ways to avoid the joins, you can use things such as
>> indexed views to speed up queries.
>> Don't compromise data integrity and long term viability for the sake of
>> saving a few joins.
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> "Zester" <zeze@.nottospam.com> wrote in message
>> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.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" <zeze@.nottospam.com> wrote in message
news:uIE29b3OIHA.3532@.TK2MSFTNGP04.phx.gbl...
> Thanks for your input. Hm, that's true that we can just keeping using the
> text description in the main table and just create another table to be
> referenced. So you would have 50 extra tables, but why data integrity is
> an issue when just use check constraint to make sure the set options are
> declared and reinforced? The only drawback I see so far is if we need to
> add new enum value to the set, we need to change the check constraint
> instead of just simply inserting another entry in the permission type
> definition table. However, to do the insertion, we need 50 UI pieces. I
> think there are something I should point out, we host db solution
> internally (on-site, web-based) so modifying the db constraints is an easy
> thing for us comparing with releasing db schema with the solution like
> other product.
>
With a CHECK constraint how would you enumerate the set of values in your
application? If you code them in your app as well then you have to change it
in two different places and rebuild your app just to create a new value. If
you put them in a table your application can easily retrieve them directly
so you don't need to update your app each time you create a new value.
--
David Portas|||That's a good point but I think in general we don't want to make something
dynamic unnecessarily. These enum value sets can be hard-coded in the code.
I believe C# for example can convert enum value to the string (the enum
name) to match with the db. Even if we are dealing with older language, we
can have a layer to do these translations; it's far cheaper than create a UI
to do that for each additional table. Adding a new enum value is not
something should be happening frequently; it breaks switch statement and
if-else combo's very often and create bugs; so the cost of modifying the
contraint is small relative to a total cost of adding a new value because we
always detect it that we need to change it at a small amount of testing.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OXyMip3OIHA.3516@.TK2MSFTNGP02.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:uIE29b3OIHA.3532@.TK2MSFTNGP04.phx.gbl...
>> Thanks for your input. Hm, that's true that we can just keeping using the
>> text description in the main table and just create another table to be
>> referenced. So you would have 50 extra tables, but why data integrity is
>> an issue when just use check constraint to make sure the set options are
>> declared and reinforced? The only drawback I see so far is if we need to
>> add new enum value to the set, we need to change the check constraint
>> instead of just simply inserting another entry in the permission type
>> definition table. However, to do the insertion, we need 50 UI pieces. I
>> think there are something I should point out, we host db solution
>> internally (on-site, web-based) so modifying the db constraints is an
>> easy thing for us comparing with releasing db schema with the solution
>> like other product.
> With a CHECK constraint how would you enumerate the set of values in your
> application? If you code them in your app as well then you have to change
> it in two different places and rebuild your app just to create a new
> value. If you put them in a table your application can easily retrieve
> them directly so you don't need to update your app each time you create a
> new value.
> --
> David Portas
>