[SOLVED] columns with more than one variable

[matthew_ellis24]

Hi, I'm trying to set up a directory of habitat types, that also displays what species are found in association with them. I think I need my table to be set up like this:

habitat_id

habitat_name

habitat_description

habitat_countries

 

and then in the species table have a link to the habitat_id field.

 

There are a few problems, but I think they're essentially the same:

Species can occur in more than one habitat. So how do I enter that in the species table? Can I have 1, 2, 3 (for habitat ids) in the same column, or do I need to have multiple columns to allow for multiple number of habitats? And then the same problem with multiple countries having similar habitats. I'm just worried that if I have 1, 2, 3 and I search for "like 1" will I not then recall all the 10...19, 21...91 etc etc?

 

The ultimate goal being to allow a user to select:

- habitats, the countries it's found in and the species found in it

- countries and the habitats found in them

- species and the habitats it's found in

Doing the above should be easy once I'm certain of the database tables

 

thanks

Matt

[fenway]

You don't enter that in the species table -- you make a new table that ties species to habitats.  Check out the sticky on db normalization.

[matthew_ellis24]

so I need to have something like this, where this is my habitat table:

<pre>

habitat_id    habitat_name                habitat_text

1             <i>Calluna-Erica</i> heath     <i>Calluna-Erica</i> heath consists of scrubby hea...

2             Deciduous Forest                 <i>Quercus</i> and <i>Fagus</i> etc etc

</pre>

 

and this is the table that relates habitats to species?

<pre>

habitat_species_id    habitat_id    species_id

1                             1                   9

2                             1                   37

3                             2                   37

4                             2                   1

</pre>

 

My only concern is this seems a pain to do anything with. For instance if I add a new habitat then I need to add it in the habitat table, and then add (in some instances) up to 200 new rows to the habitat_species table.

 

Then again, if it's the best way to do it, it's the best way to do it...

 

thanks for your help

[fenway]

That's the best way... you can always "copy" existing habitat relationship records with an insert into.. select where ... and just replace the h_id.

[matthew_ellis24]

there aren't any more records, I just inserted those ones to have a play with it.

 

Thanks for your help, I'll get right on it.

 

Many thanks

Matt