setting up efficiently changeable table

turpentyne

As a newbie, I'm still getting my head wrapped around databases and php. So I might need to backtrack and rethink a couple things. One of those is a table that needs to be designed to make potential changes easy.

If I'm understanding this right, it's a one-to-many relationship that shows the scientific name, and hierarchal genus, family, division, etc. of plants. But my uncertainty on how to build this is that that organization can change. The plant name will always stay the same, but thanks to new research, a branch on the tree seems to get relocated every couple years. Or worse, eliminated/lumped in with another.

I don't want to have to make changes to 300 plants if I can make just one. But I don't know for sure how to set it up. Right now I have one table with a column for the plant id and each level of the 'family tree'. I'm almost certain that is wrong. Should I make a separate table for every level of identification? Treat it like a many-to-many? Can anybody clear this up for a still-learning newbie?

paulnaj

If I understand correctly, then ...

a plant belongs to a single division
a division belongs to single family
a family belongs to single genus

These all seem to be one-to-many relationships, assuming it's not possible for a plant to be in two divisions (at the same time).

So, I would have a division field in the plant table, a family field in the division table, etc.

This gives the maximum flexibility since, if a plant needs relocating to a new division then only a single change is necessary, if a whole division of plants gets moved to a new family, then that is only a single change ... etc.

Hope that helps.

turpentyne

Ok, that's how I have it set up now. But if it's an entire division that get's moved, wouldn't I have to go in to change every single plant with that word in the division field?

Or am I missing some sort of one-step 'batch' change that can be done?

laserlight

turpentyne wrote:
But if it's an entire division that get's moved, wouldn't I have to go in to change every single plant with that word in the division field?

Or am I missing some sort of one-step 'batch' change that can be done?

When declaring the foreign key (i.e., the division field), specify ON UPDATE CASCADE.

turpentyne

I just realized I didn't pay full attention to that message. I see. So I should just have the lowest level in the plant table, then one table for each level up?

turpentyne

Actually that makes it simple. since I already built individual tables so I could populate a series of dynamic dropdowns that change depending on what is selected.

paulnaj

turpentyne;10959734 wrote:
So I should just have the lowest level in the plant table, then one table for each level up?

Yup, maybe the clearest way to see it is to note that ...

One genus can have many families - a family can only have one genus.
One family can have many divisions - a division can only have one family.
One division can have many plants- a plant can only have one division.

These are one-to-many relationships. Setting it up this way is as flexible as you can get (even though there might be times where you do have to make changes to individual plants, even those in the same division).