[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Basic DB Design Question

Ryan

3/29/2007 8:43:00 PM

Double Column tables (ID and Field).. should they be used?
For example, lets say I have a Person table. Some attributes could be
Ethnicity, State, EyeColor, and HairColor. If I want the user to have the
ability to modify the available selections for those fields, the way I've
done it in the past is create a new table. So for example now in the Person
table I'd have the foreign keys EthnicID, StateID, etc and 4 new tables.
Each new table would contain only 2 columns, the ID column and the attribute
column. The advantages I see for this are:
A) Any time a value is changed, say in the Ethnic Table the row (attribute)
White is changed to Caucasian, the change is reflected across every Person
record. B) Pretty easy to handle with lookup tables. The disadvantage is
the tables are more obscure and I end up having to write a lot more Views
and complex joins in Queries. Is there a standard practice for this sort of
thing?

Thanks,
Ryan


7 Answers

David Portas

3/29/2007 8:55:00 PM

0

On 29 Mar, 21:42, "Ryan" <Tyv...@newsgroups.nospam> wrote:
> Double Column tables (ID and Field).. should they be used?
> For example, lets say I have a Person table. Some attributes could be
> Ethnicity, State, EyeColor, and HairColor. If I want the user to have the
> ability to modify the available selections for those fields, the way I've
> done it in the past is create a new table. So for example now in the Person
> table I'd have the foreign keys EthnicID, StateID, etc and 4 new tables.
> Each new table would contain only 2 columns, the ID column and the attribute
> column. The advantages I see for this are:
> A) Any time a value is changed, say in the Ethnic Table the row (attribute)
> White is changed to Caucasian, the change is reflected across every Person
> record. B) Pretty easy to handle with lookup tables. The disadvantage is
> the tables are more obscure and I end up having to write a lot more Views
> and complex joins in Queries. Is there a standard practice for this sort of
> thing?
>
> Thanks,
> Ryan

What else did you think foreign keys were for? :-)

There is a very large body of literature dealing with database design.
It will be well worth your while to do some reading. Two of my
favourites are:

http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&EAN=978...
http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&EAN=978...

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

Ryan

3/29/2007 9:07:00 PM

0

Well primarily all the literature I've read on database design refers to
separate tables as complete entities, which (I would think) would entail
more than a single property (and every sample I've seen, it does). I've
done a lot of database design in a small-medium size production environment
(100+ users) and I'm very familiar with the rules of database normalization
(up to 4th or even 5th). However I've never seen anything in regards to
what this would seem like a very common situation... or maybe I just have
selective reading disease ;) Thanks for the book links. Probably would be
a good idea for me to add them to my collection.

Ryan

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1175201725.273269.280870@b75g2000hsg.googlegroups.com...
> On 29 Mar, 21:42, "Ryan" <Tyv...@newsgroups.nospam> wrote:
>> Double Column tables (ID and Field).. should they be used?
>> For example, lets say I have a Person table. Some attributes could be
>> Ethnicity, State, EyeColor, and HairColor. If I want the user to have
>> the
>> ability to modify the available selections for those fields, the way I've
>> done it in the past is create a new table. So for example now in the
>> Person
>> table I'd have the foreign keys EthnicID, StateID, etc and 4 new tables.
>> Each new table would contain only 2 columns, the ID column and the
>> attribute
>> column. The advantages I see for this are:
>> A) Any time a value is changed, say in the Ethnic Table the row
>> (attribute)
>> White is changed to Caucasian, the change is reflected across every
>> Person
>> record. B) Pretty easy to handle with lookup tables. The disadvantage
>> is
>> the tables are more obscure and I end up having to write a lot more Views
>> and complex joins in Queries. Is there a standard practice for this sort
>> of
>> thing?
>>
>> Thanks,
>> Ryan
>
> What else did you think foreign keys were for? :-)
>
> There is a very large body of literature dealing with database design.
> It will be well worth your while to do some reading. Two of my
> favourites are:
>
> http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&EAN=978...
> http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&EAN=978...
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
> --
>
>
>
>
>
>


Anith Sen

3/29/2007 10:08:00 PM

0

>> Well primarily all the literature I've read on database design refers to
>> separate tables as complete entities, which (I would think) would entail
>> more than a single property (and every sample I've seen, it does).

It is somewhat hard to formally define an entity. While ethnicity can be
treated as an attribute from a certain perspective, one could potentially
consider ethnicity to be an entity given that it can be conceived of having
multiple properties -- ethnicity identifier, ethnicity description,
ethnicity status etc..

This is one of the primary factors for so many designers to favor Halpin's
OR modeling approach ( the first book reference by David ) over ER model
these days.

Considering the practical limitations of type support in SQL, the common
advice is often based on the time variant nature of the list of property
values. If the list is time varying, you should consider using a table to
represent them. If the list is static and has reasonably small number of
elements, you might consider representing them as an attribute in a table,
with a CHECK constraint.

--
Anith


Roy Harvey

3/29/2007 10:09:00 PM

0

There is indeed a standard practice, and you are already following it.
Considering the astounding number and variety of questionable database
implementations that are commonly described here you are to be
congratulated, first for getting it right and second for checking
before messing it up. 8-)

Roy Harvey
Beacon Falls, CT

On Thu, 29 Mar 2007 15:42:49 -0500, "Ryan" <Tyveil@newsgroups.nospam>
wrote:

>Double Column tables (ID and Field).. should they be used?
>For example, lets say I have a Person table. Some attributes could be
>Ethnicity, State, EyeColor, and HairColor. If I want the user to have the
>ability to modify the available selections for those fields, the way I've
>done it in the past is create a new table. So for example now in the Person
>table I'd have the foreign keys EthnicID, StateID, etc and 4 new tables.
>Each new table would contain only 2 columns, the ID column and the attribute
>column. The advantages I see for this are:
>A) Any time a value is changed, say in the Ethnic Table the row (attribute)
>White is changed to Caucasian, the change is reflected across every Person
>record. B) Pretty easy to handle with lookup tables. The disadvantage is
>the tables are more obscure and I end up having to write a lot more Views
>and complex joins in Queries. Is there a standard practice for this sort of
>thing?
>
>Thanks,
>Ryan
>

David Portas

3/29/2007 10:15:00 PM

0

On 29 Mar, 22:07, "Ryan" <Tyv...@newsgroups.nospam> wrote:
> Well primarily all the literature I've read on database design refers to
> separate tables as complete entities, which (I would think) would entail
> more than a single property (and every sample I've seen, it does).

This is one of those occassions when it helps to understand that a
table is a predicate and its rows are a set of propositions, even
though "entity" is a very common and useful shorthand for what we mean
by those propositions.

At the risk of starting a fruitless debate it's worth mentioning that
the inclusion or absence of a surrogate key has nothing to do with
normalization per se. In the absence of a surrogate key you could
achieve the same result by using a CHECK constraint to define the set
of values in each case. Of course it is highly convenient to create
the "extra" tables in many cases since the set of values isn't always
defined at design time.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--






> I've
> done a lot of database design in a small-medium size production environment
> (100+ users) and I'm very familiar with the rules of database normalization
> (up to 4th or even 5th). However I've never seen anything in regards to
> what this would seem like a very common situation... or maybe I just have
> selective reading disease ;) Thanks for the book links. Probably would be
> a good idea for me to add them to my collection.
>
> Ryan
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote in message
>
> news:1175201725.273269.280870@b75g2000hsg.googlegroups.com...
>
>
>
> > On 29 Mar, 21:42, "Ryan" <Tyv...@newsgroups.nospam> wrote:
> >> Double Column tables (ID and Field).. should they be used?
> >> For example, lets say I have a Person table. Some attributes could be
> >> Ethnicity, State, EyeColor, and HairColor. If I want the user to have
> >> the
> >> ability to modify the available selections for those fields, the way I've
> >> done it in the past is create a new table. So for example now in the
> >> Person
> >> table I'd have the foreign keys EthnicID, StateID, etc and 4 new tables.
> >> Each new table would contain only 2 columns, the ID column and the
> >> attribute
> >> column. The advantages I see for this are:
> >> A) Any time a value is changed, say in the Ethnic Table the row
> >> (attribute)
> >> White is changed to Caucasian, the change is reflected across every
> >> Person
> >> record. B) Pretty easy to handle with lookup tables. The disadvantage
> >> is
> >> the tables are more obscure and I end up having to write a lot more Views
> >> and complex joins in Queries. Is there a standard practice for this sort
> >> of
> >> thing?
>
> >> Thanks,
> >> Ryan
>
> > What else did you think foreign keys were for? :-)
>
> > There is a very large body of literature dealing with database design.
> > It will be well worth your while to do some reading. Two of my
> > favourites are:
>
> >http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y......
> >http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y......
>
> > --
> > David Portas, SQL Server MVP
>
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
>
> > SQL Server Books Online:
> >http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
> > --- Hide quoted text -
>
> - Show quoted text -

AK

3/29/2007 10:43:00 PM

0

On Mar 29, 3:42 pm, "Ryan" <Tyv...@newsgroups.nospam> wrote:
> Double Column tables (ID and Field).. should they be used?
> For example, lets say I have a Person table. Some attributes could be
> Ethnicity, State, EyeColor, and HairColor. If I want the user to have the
> ability to modify the available selections for those fields, the way I've
> done it in the past is create a new table. So for example now in the Person
> table I'd have the foreign keys EthnicID, StateID, etc and 4 new tables.
> Each new table would contain only 2 columns, the ID column and the attribute
> column. The advantages I see for this are:
> A) Any time a value is changed, say in the Ethnic Table the row (attribute)
> White is changed to Caucasian, the change is reflected across every Person
> record. B) Pretty easy to handle with lookup tables. The disadvantage is
> the tables are more obscure and I end up having to write a lot more Views
> and complex joins in Queries. Is there a standard practice for this sort of
> thing?
>
> Thanks,
> Ryan

Ryan,

Another alternative, less common but still worth mentioning, is not to
have ID column in you Ethnic table at all.
You can have your Person.EthnicName column refer directly to
Ethnic.Name column, and have ON UPDATE CASCADE to make sure both
columns are always in sync.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blo...
http://sqlserver-puzzles.blo...

Ryan

3/30/2007 12:42:00 PM

0

Thanks Roy, and all. That seems to be the consensus here, that there's
nothing wrong with the way I'm doing it now. I'm glad I checked though as
it certainly raised an enlightening (for me) discussion.

Ryan

"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:a1eo03djkqnkrr21f0g5pnc9ep2mvfm0h1@4ax.com...
> There is indeed a standard practice, and you are already following it.
> Considering the astounding number and variety of questionable database
> implementations that are commonly described here you are to be
> congratulated, first for getting it right and second for checking
> before messing it up. 8-)
>
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 29 Mar 2007 15:42:49 -0500, "Ryan" <Tyveil@newsgroups.nospam>
> wrote:
>
>>Double Column tables (ID and Field).. should they be used?
>>For example, lets say I have a Person table. Some attributes could be
>>Ethnicity, State, EyeColor, and HairColor. If I want the user to have the
>>ability to modify the available selections for those fields, the way I've
>>done it in the past is create a new table. So for example now in the
>>Person
>>table I'd have the foreign keys EthnicID, StateID, etc and 4 new tables.
>>Each new table would contain only 2 columns, the ID column and the
>>attribute
>>column. The advantages I see for this are:
>>A) Any time a value is changed, say in the Ethnic Table the row
>>(attribute)
>>White is changed to Caucasian, the change is reflected across every Person
>>record. B) Pretty easy to handle with lookup tables. The disadvantage is
>>the tables are more obscure and I end up having to write a lot more Views
>>and complex joins in Queries. Is there a standard practice for this sort
>>of
>>thing?
>>
>>Thanks,
>>Ryan
>>