Need advice. Dropdown tables and moving data

Hi,
I have the following problem. In my system, I'm going to have many
tables that use drop-down tables. For example, my 'Persons' table has a
column called 'FK_HairColors_ID' which is a FK of the table 'HairColors'
If I want to add a new person calld 'John' with black hair, I do this:
insert into haircolors (id,color) values (1,'brown')
insert into haircolors (id,color) values (2,'black')
insert into persons (siteid,id,name,fk_haircolors_id) values ('site 1'
1,'john',2)
I also though that instead of doing this, I could insert the value
directly there. Something like this:
insert into haircolors (pk) values ('brown')
insert into haircolors (pk) values ('black')
insert into persons (siteid,id,name,fk_haircolors_pk) values ('site
1',1,'john','brown')
And I would add a relation between those tables, so the the system would
check that the values that I have for the hair color in Persons are in
the HairColors table.
Obviously, the first solution is more elegant. But maybe for my problem
it will be better the second solution.
Here is my problem.
I am going to have multiple sites running this type of db. We are going
to have some processes that will transfer records from one site to
another. That's why we have that 'siteid' field there for Persons. My
question is:
Should I have siteid for the haircolors as well? (I mean to add a column
'SiteID' in the HairColors table) Does that make sense? In that case we
could have 2 'black' hair color records that came from different sites.
If not, everytime that I move a Person from one site to another, I would
have to 'reconvert' the id of the Haircolor to the one matching in the
new site. For example, if for 'site 1' the black hair color had ID=2,
and for 'site 2' has ID=200, and I moving a record from 1 to 2, I would
have to convert that ID from 2 to 200.
What do you guys think?
Thanks for reading everything. I know it's a little bit confusing, but I
hope you guys got the idea of my problem.
RegardsStar wrote:
> Hi,
> I have the following problem. In my system, I'm going to have many
> tables that use drop-down tables. For example, my 'Persons' table has a
> column called 'FK_HairColors_ID' which is a FK of the table 'HairColors'
> If I want to add a new person calld 'John' with black hair, I do this:
> insert into haircolors (id,color) values (1,'brown')
> insert into haircolors (id,color) values (2,'black')
> insert into persons (siteid,id,name,fk_haircolors_id) values ('site 1'
> 1,'john',2)
> I also though that instead of doing this, I could insert the value
> directly there. Something like this:
> insert into haircolors (pk) values ('brown')
> insert into haircolors (pk) values ('black')
> insert into persons (siteid,id,name,fk_haircolors_pk) values ('site
> 1',1,'john','brown')
> And I would add a relation between those tables, so the the system would
> check that the values that I have for the hair color in Persons are in
> the HairColors table.
> Obviously, the first solution is more elegant. But maybe for my problem
> it will be better the second solution.
> Here is my problem.
> I am going to have multiple sites running this type of db. We are going
> to have some processes that will transfer records from one site to
> another. That's why we have that 'siteid' field there for Persons. My
> question is:
> Should I have siteid for the haircolors as well? (I mean to add a column
> 'SiteID' in the HairColors table) Does that make sense? In that case we
> could have 2 'black' hair color records that came from different sites.
> If not, everytime that I move a Person from one site to another, I would
> have to 'reconvert' the id of the Haircolor to the one matching in the
> new site. For example, if for 'site 1' the black hair color had ID=2,
> and for 'site 2' has ID=200, and I moving a record from 1 to 2, I would
> have to convert that ID from 2 to 200.
> What do you guys think?
> Thanks for reading everything. I know it's a little bit confusing, but I
> hope you guys got the idea of my problem.
> Regards
"Dropdown tables"! (shudder) Do you really believe that the job of
tables is to model a user-interface for you?
Neither of your proposed solutions solves the problem you are getting
at. That is: "How do I ensure that all my sites encode hair colours in
the same way?" For example, does "Blond"="Blonde"? Does "Brown" mean
the same as "Brunette"? If you leave decisions like that to end users
then you will have a big task on your hands when you come to integrate
your data. If you have a standard set of codes that is centrally
maintained then you won't have that problem whatever method you use
(but do please choose a recognized standard coding convention if one
exists for your data).
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/library/ms130214(en-US,SQL.90).aspx
--|||David,
We cannot have a centralized site that can provide nice codes to each
one. Some sites will be working as an standalone sites without access
outside their network. We don't know if in the future they may be
interested in sharing some data. If they are later, they will provide
access to the remote sites temporarily, while the transfer is being made.
On the other hand, users want to be able to add new values to those
'dropdown tables' at any time.
In the hypothetical case that we could have a centralized site, some
sites wouldn't be interested in seeing the 'set of codes' from the
other 40 sites, when they have to select a value. Had you thought about
that?. Some 'drop-down tables' are not as simple as 'Hair Colors'... For
example, some may be 'Arrest codes' for a specific police department. We
don't want to have site 1 to see the arrest codes from each one of the
sites!! (shudder)
For that reason, and before shuddering and criticizing take a look at
the real world with real customers. It's great to answer the newsgroups
with 3 o 4 things that you got from the books that you have read, but in
the real world things are not so 'standard' and cannot be made in the
most elegant way.
David Portas wrote:
> Star wrote:
>
>
> "Dropdown tables"! (shudder) Do you really believe that the job of
> tables is to model a user-interface for you?
> Neither of your proposed solutions solves the problem you are getting
> at. That is: "How do I ensure that all my sites encode hair colours in
> the same way?" For example, does "Blond"="Blonde"? Does "Brown" mean
> the same as "Brunette"? If you leave decisions like that to end users
> then you will have a big task on your hands when you come to integrate
> your data. If you have a standard set of codes that is centrally
> maintained then you won't have that problem whatever method you use
> (but do please choose a recognized standard coding convention if one
> exists for your data).
> --
> 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/library/ms130214(en-US,SQL.90).aspx
> --
>|||For your hair color example I would forget using an artificial key
(HairColorID) and just use 'brown' or 'black' as the value. I do not
find using a number to be "elegent", though it is sometimes necessary.
For something more complicated, as you described in your second
message, I would be inclined to make the key two part, site and
ArrestCode. But I would have to see what sort of data went into an
ArrestCode master table before choosing the key.
Also, if you provide a simple example that is not representative of
the real problem, don't get too critical of the answers that reflect
your question as asked instead of the real problem. I also do not
know why you would assume that any particular advice is based on
examples in books. Believe it or not a lot of answers here are based
on years of hard won experience. For myself, just about any approach
I suggest avoiding is because I am trying to help others avoid my own
past mistakes. 8-)
Roy Harvey
Beacon Falls, CT
On Tue, 28 Mar 2006 22:46:58 +0200, Star <star@.nospam.com> wrote:

>Hi,
>I have the following problem. In my system, I'm going to have many
>tables that use drop-down tables. For example, my 'Persons' table has a
>column called 'FK_HairColors_ID' which is a FK of the table 'HairColors'
>If I want to add a new person calld 'John' with black hair, I do this:
>insert into haircolors (id,color) values (1,'brown')
>insert into haircolors (id,color) values (2,'black')
>insert into persons (siteid,id,name,fk_haircolors_id) values ('site 1'
>1,'john',2)
>I also though that instead of doing this, I could insert the value
>directly there. Something like this:
>insert into haircolors (pk) values ('brown')
>insert into haircolors (pk) values ('black')
>insert into persons (siteid,id,name,fk_haircolors_pk) values ('site
>1',1,'john','brown')
>And I would add a relation between those tables, so the the system would
>check that the values that I have for the hair color in Persons are in
>the HairColors table.
>Obviously, the first solution is more elegant. But maybe for my problem
>it will be better the second solution.
>Here is my problem.
>I am going to have multiple sites running this type of db. We are going
>to have some processes that will transfer records from one site to
>another. That's why we have that 'siteid' field there for Persons. My
>question is:
>Should I have siteid for the haircolors as well? (I mean to add a column
>'SiteID' in the HairColors table) Does that make sense? In that case we
>could have 2 'black' hair color records that came from different sites.
>If not, everytime that I move a Person from one site to another, I would
>have to 'reconvert' the id of the Haircolor to the one matching in the
>new site. For example, if for 'site 1' the black hair color had ID=2,
>and for 'site 2' has ID=200, and I moving a record from 1 to 2, I would
>have to convert that ID from 2 to 200.
>What do you guys think?
>Thanks for reading everything. I know it's a little bit confusing, but I
>hope you guys got the idea of my problem.
>Regards|||Thanks for your answer, Roy. I think I will end up using your
first suggestion.
Regarding David's answer... I understand that I could have given more
information about my problem, like I did in my second post, however
there are many ways of helping, and this was not the best way of doing it:

>"Dropdown tables"! (shudder) Do you really believe that the job of
>tables is to model a user-interface for you?
Don't you think? If he needed more information he could have asked
and I would have explained why I needed to have those shuddering
'Dropdown tables'. It's just I didn't think it was necessary to explain
all the reasons I was doing that. I just wanted to mention which one was
my problem.
Thanks again for helping, Roy.
Roy Harvey wrote:
> For your hair color example I would forget using an artificial key
> (HairColorID) and just use 'brown' or 'black' as the value. I do not
> find using a number to be "elegent", though it is sometimes necessary.
> For something more complicated, as you described in your second
> message, I would be inclined to make the key two part, site and
> ArrestCode. But I would have to see what sort of data went into an
> ArrestCode master table before choosing the key.
> Also, if you provide a simple example that is not representative of
> the real problem, don't get too critical of the answers that reflect
> your question as asked instead of the real problem. I also do not
> know why you would assume that any particular advice is based on
> examples in books. Believe it or not a lot of answers here are based
> on years of hard won experience. For myself, just about any approach
> I suggest avoiding is because I am trying to help others avoid my own
> past mistakes. 8-)
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 28 Mar 2006 22:46:58 +0200, Star <star@.nospam.com> wrote:
>|||Star wrote:
> We cannot have a centralized site that can provide nice codes to each
> one. Some sites will be working as an standalone sites without access
> outside their network. We don't know if in the future they may be
> interested in sharing some data. If they are later, they will provide
> access to the remote sites temporarily, while the transfer is being made.
> On the other hand, users want to be able to add new values to those
> 'dropdown tables' at any time.
In that case you will have integrate and conform the data at some
future date under either of your suggested solutions. I thought you
were asking whether you could avoid that but apparently you cannot.

> In the hypothetical case that we could have a centralized site, some
> sites wouldn't be interested in seeing the 'set of codes' from the
> other 40 sites, when they have to select a value. Had you thought about
> that?. Some 'drop-down tables' are not as simple as 'Hair Colors'... For
> example, some may be 'Arrest codes' for a specific police department. We
> don't want to have site 1 to see the arrest codes from each one of the
> sites!! (shudder)
That's a different question to the one you originally asked. In that
case you would presumably implement another table that mapped Arrect
Codes to Sites so you could track which codes each site could see.

> For that reason, and before shuddering and criticizing take a look at
> the real world with real customers. It's great to answer the newsgroups
> with 3 o 4 things that you got from the books that you have read, but in
> the real world things are not so 'standard' and cannot be made in the
> most elegant way.
I have been working with real customers' for 15 years.
The only criticism I meant to imply in my post was about your use of
the term "dropdown tables". What do you mean by that? Obviously it's
not a standard term. My interpretation was that you called them by that
name because they are used for dropdown lists in your UI. This is not a
good way to look at database design. In fact that mode of thinking
(database follows UI) leads to a lot of mistakes by developers in my
experience. Database design should proceed from business analysis to
conceptual modelling to logical modelling to physical modelling. In
that sense there is only one type of table.
So I'm not saying your solutions are right or wrong because your
example doesn't give us any criteria for that decision. What I was
suggesting was that your use of the term "dropdown table" indicates
that you may not have the right understanding of some database design
concepts.
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/library/ms130214(en-US,SQL.90).aspx|||>Regarding David's answer... I understand that I could have given more
>information about my problem, like I did in my second post, however
>there are many ways of helping, and this was not the best way of doing it:
>
>Don't you think?....
As it happens, I see nothing wrong with Dave's answer. But everyone
sees things their own way.
Roy Harvey
Beacon Falls, CT|||Star skrev:

> Regarding David's answer... I understand that I could have given more
> information about my problem, like I did in my second post, however
> there are many ways of helping, and this was not the best way of doing it:
>
Just to settle this ;)
Star, frankly I think you over-reacted, don't be so sensitive, just
google for CELKO in this newsgroup and be thankful he didn't answer
your question (not yet, anyway).
Anyway, when asking for help on a newsgroup you should prepare yourself
for all sorts of possible answers, remember people are trying to answer
without getting paid or anything, and we all have our ups and downs.
Just keep your own calm, and in the end you probably get some good
answers, for free!
/impslayer, aka Birger Johansson|||impslayer,
yep... I have seen some of Celko's posts. I think he never tries to
help. In my opinion, he usually does the following:
1. Try to ridicule the person asking the question.
2. Recomend the person to buy his 'wonderful' books.
3. If he feels like helping, he will post a solution so theoretical that
will discourage the original poster. He would provide academic exercises
that have little application in the real world.
Regards|||
> The only criticism I meant to imply in my post was about your use of
> the term "dropdown tables". What do you mean by that? Obviously it's
> not a standard term. My interpretation was that you called them by that
> name because they are used for dropdown lists in your UI. This is not a
> good way to look at database design.
David,
Maybe the term 'drop-down tables' was not the best to use. I don't speak
English very well, and I didn't know a better way to say that.
Basically our customers needs to see a drop-down box in the UI with
different values to choose from for some of the fields. That information
needs to be stored in a table, don't you think?. That's why I called
like that. How would you call it?