Design Question: column storing a type

Zester

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

DCPeterson

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

David Portas

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

Zester

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