normalize or not ?

[BubikolRamios]

table containing data about animals, plants. Plants allso have info on flower, leaves color ...

table 1

key f_color l_color
1 null null
2 text text
3 null null
4 null null
5 text text
..



If I move f_color,l_color to another table
then this will be 1 to 1 relationship.
Should I do normalization or not ?

[bazz]

please post the relevant tbales and I'll see if I can organise/normalise it.

bazz

[Fumigator]

I would say if you have other details about the color you want to express, other than the name of the color, then break the colors into another table that stores the color name and color details (or whatever). Otherwise, if it's just a color name, you don't need a separate table for it.

One thing you probably will want to do, however, is make sure the color names are stored consistently. For example, for the color yellow-green you don't want values a value of green-yellow to slip in there. Nor do you want color names misspelled. For this validation you may want to store "valid color names" in a separate table.

[Old Pedant]

I would say "yes". You don't want somebody entering "lightgray" when the correct spelling (as used in browsers) is "lightgrey". Even if you aren't talking about browser colors, just the fact that you are protected agains spelling mistakes is worth it.

Having said that: If you opted to not go normalized for something like this, it wouldn't be the worst thing in the world, by far.

Clearly some people go way too far with normalization. I once saw this in a DB design:

Table: person
...
genderid : int, foreign key to gender table
...

Table: gender
genderid : int, pk
genername : char(1)


Yep, you guessed it: The gender table consisted of

1 :: M
2 :: F

Silly, right?

So having "7" in place of "red" isn't really saving space in the DB. It's more a matter of worrying about spelling differences. And if you are getting the spelling from, say, a <SELECT>, then that seems like a non-worry.

[Fumigator]

I just realized though, you are storing multiple colors for a plant or animal. (I'm not sure how that fact missed my consciousness the first time around, lol.)

I would say if you wanted to store a different number of colors for each item, then definitely put it all in a separate table. So for example, if an animal needs 4 colors to describe it, then your colors table would contain 4 rows for that animal. If a plant had only one color to describe it, then the colors table would contain only 1 row for that plant.

[Old Pedant]

Hmmm...that's a tougher call, I think.

If you only had, for example "foreground_color" and "background_color" for a type of object, then I would say it *might* be okay to put them directly in the main record, for most usages.

It *DOES* mean that if you need to answer a query such as "Find all objects that have yellow for any of their colors" then you can't just query against a single column in a single table. If there's even an inkling that you might want/need to do such a cross-column query, you clearly should follow Fumigator's advice.

Ehhh...but maybe the safe thing to do is to follow his advice, period. Just in case. Could well save you grief in the long run.

And this could well be a case where you will end up with some one-to-one tables!

Example:

Table: FloraAndFauna
fid : int, PK
... characteristics common to all plants and animals ...

Table: Flora
fid : int, FK to FloraAndFauna table
... flora-only characteristics ...

Table: Fauna
fid : int, FK to FloraAndFauna table
... fauna-only characteristics ...

Table: FFColors
fid : int, FK to FloraAndFauna table
colorTypeID : int, FK to color types table
colorID : int, FK to colors table

Table: ColorTypes
colorTypeId : int, PK
colorType : text (e.g., "leaf color", "skin color", "fur color")

Table: Colors
colorID : int, PK
color : text ("pink", "brown", etc.)

....


Your Flora table and your Fauna table will be 1-to-1 with your FloraAndFauna table, but the existence of the FandF table simplifies enormously the problem of handling such common characteristics as color, as shown here.

This is when you wish you were using an Object Oriented DBMS instead of a relational one. You'd just use class mapping and be done!

class FloraAndFauna
{
FFColor colors[];
...
}
class Flora : public FloraAndFauna
{
...
}
class FFColor
{
ColorType colortype;
Color color;
}
... and so on ...

Just pass that class hierarchy to the schema generator for the OODBMS and voila! You are done.

[bazz]

We need a new forum!! called "Unique helpfulleness at CF"

We need to be able to copy posts/threads from a forum to the new forum so new people can see why it would be good to post here AND where established people can be reminded about how good CF is.

Here we have had two people suggest and explain what the OP should consider doing when they have a received snippet of info. The OP was limited in their knowledge and the responders jumped into the 'breach'. Hats off to them! Hats off the the OP too.

I recall being an absolute beginner (theres a song about that :) ) and such help made me feel like 'someday; I might be able to do something useful'.

I am grateful for the help. I am humbled by the generosity of people who 'give back'.

bazz

[Old Pedant]

Well, gosh, I just thought it was a fun and interesting discussion. Yes, it's a good question. The "correct" answer is surely to normalize, but there are clearly times when "correct" doesn't mean "best." Fun stuff.