Database design question...

[Member]

I have a situation that some of you may be of help. I will name the tables
specifically so you can understand what I am talking about.

I have a COS_species table....the information in it cannot be changed.

Everything in this database is pretty much centered around the speciesID. We
also want other species that are no included in the COS_Species table.
Specifically... .we want to be able to handle multi species which would
contain many of the species from COS_Species.

So...I would create a new table called Multi_Species and then a linking
table MultiSpeciesLin k to link the 2 tables together.

The problem is....since everything is supposed to be centered around the
SpeciesID, how do I manage that since a speciesID can come from 2 different
tables?

Thanks for any input.

[Daniel Morgan]

Re: Database design question...

Member wrote:
[color=blue]
>I have a situation that some of you may be of help. I will name the tables
>specifically so you can understand what I am talking about.
>
>I have a COS_species table....the information in it cannot be changed.
>
>Everything in this database is pretty much centered around the speciesID. We
>also want other species that are no included in the COS_Species table.
>Specifically.. ..we want to be able to handle multi species which would
>contain many of the species from COS_Species.
>
>So...I would create a new table called Multi_Species and then a linking
>table MultiSpeciesLin k to link the 2 tables together.
>
>The problem is....since everything is supposed to be centered around the
>SpeciesID, how do I manage that since a speciesID can come from 2 different
>tables?
>
>Thanks for any input.
>
>
>[/color]
You have a major design problem. That the speciesID can come from two
different tables is the problem
that must be solved first. No doubt Joe Celko can quote you chapter and
verse on this one so before he
does you might want to check some of his numerous posts on what a table
is and make the appropriate
change.

What you describe will turn into a reporting nightmare if it is not
quickly remedied.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)