child tables...yes or no

[teedoff]

I was told on another forum that my table was not set up properly. Specifically, two of my columns, "keywords" and "catagory" should be in child tables. I just dont see that, or I dont understand why I would need to do this for these columns. here is my info from a SHOW CREATE table:

rlbbulbs CREATE TABLE `rlbbulbs` (
 `bulbID` int(11) NOT NULL AUTO_INCREMENT,
 `brandID` int(11) NOT NULL,
 `item` char(255) DEFAULT NULL,
 `bulbDesc` longtext,
 `price` char(255) DEFAULT NULL,
 `qtyPerPrice` char(255) DEFAULT NULL,
 `wattage` char(255) DEFAULT NULL,
 `voltage` char(255) DEFAULT NULL,
 `base` char(255) DEFAULT NULL,
 `glass` char(255) DEFAULT NULL,
 `filament` char(255) DEFAULT NULL,
 `avgLife` char(255) DEFAULT NULL,
 `beamAngle` char(255) DEFAULT NULL,
 `catagory` char(255) DEFAULT NULL,
 `keyWords` longtext,
 `image1` char(255) DEFAULT NULL,
 PRIMARY KEY (`bulbID`),
 KEY `item` (`item`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1595 DEFAULT CHARSET=latin1

I was trying to find information on child tables. The problem I am having though is that the column "keywords" are for meta tag keywords my employer uses for google search. They are a list of keywords and phrases, alphanumeric, and vary from record to record.

If I were to follow what I think the other forum is suggestion, I would need to put each keyword in a separate table or column? Am I wrong on this point? Do I need a child table for the keywords column?

Catagory column I could actually see in a child table. As each bulb is in a category. Now I dont know for sure if a bulb can be in two or more catagories, so I would need to further research this.

Thanks for any help in advance!

[teedoff]

Anyone? I am trying to figure out if "keywords" should be in a child table as suggested by someone on another forum. I dont tbink it should, but I'm fairly new to database design, so Maybe I'm not understanding his reasoning like I should. Please, can someone give me some advice on this. Thanks!

[hielo]

If I were to follow what I think the other forum is suggestion, I would need to put each keyword in a separate table or column?

A column, NOT a table. And yes, that is exactly what I would do.

[B]Bulb[/B] 
id
...


[B]Category[/B] 
id
name


[B]
Keyword[/B] 
id
name

/* Here you record the ids, NOT the actual name of the category. If you make a typo in the category name, then you just fix the typo in the Category table, but the BulbCategory table would be unaffected.  The same rationale goes for the BuldKeyword table.*/
[B]BulbCategory[/B]
Bulb_id
Category_id

[B]BulbKeyword[/B]
Bulb_id
Category_id

[teedoff]

Thanks for the advice hielo. Just to make sure I am understanding, let me give you an example of a field value in the keywords column.

Name, as you suggested above doesnt make sense. Here is an example of one field value in keywords column.

60 watt, watts, 120 volt, volts, Director light from Philips K19 60K19DL-120V replacement light bulb, lamp GE, Osram, Wiko, Ushio, Philips, light, light bulb, replacement bulb, replacement lamp, lighting, light, lamp, bulb, replacement bulb, halogen, medical, stage, stage studio, studio, lumen, candle, theater, night club, dental, LCD, display, fixture, base, lighting, light bulbs lamp lamps bulb, 100K19/DL lihgt ligth lightbulb, 100K19/DL lightbulbs, 100K19/DL lamp, 100K19/DL bulb

They are a list of keywords and phrases, alphanumeric, and vary from record to record.

Im sorry I still dont understand why I would need to put the keywords in another table. The keywords column is simply a list of meta tag keywords that are different for each bulb. So I should create another table like the following?

keyword= table name
ID= PK
keywords= keywords from bulbs table

Sorry I am so slow to understand this! lol How do I put a list like the above in another table?

[hielo]

The advantage is that in BulbKeyword you would NOT use the actual words, but the ID. So if SOMEONE else were to notice that you misspelled the word "light" (case in point, like you did above ...100K19/DL lihgt ligth lightbulb... ), then you would just need to fix it one in the Keyword table instead of "hunting down" the error in every record in Bulb.

This is just a recommendation; it's not a "requirement", so it is ultimately up to you.

[teedoff]

Hey Hielo, thanks again for the help. The owner of the company I am working for misspelled those words on purpose. In ever record of 'keywords" there are words and variations of words, I would assume to better rank in search engines and allow for when people might misspell a word when searching. I don't know personally if this is good or common practice when trying to get high SEO, but thats what he's done. So in your point that a misspelled word might cause a problem in correcting as the tables are now, then yes I could see that. However, moving them to another table would be unnecessary simply based on that point.

I appreciate your recommendations. Don't think I am trying to dispute what you are saying, on the contrary, I am trying to learn and also to make sure I am doing this the correct and best way.

Again, if i were to remove the keywords column and place them in another table, no two keywords fields are exactly alike, so then that would create a one to one table relation between my current table "rlbbulbs" and the new table "bulbKeywords".

[hielo]

The owner of the company I am working for misspelled those words on purpose.

Understood.

Again, if i were to remove the keywords column and place them in another table, no two keywords fields are exactly alike,...

agree, but you are taking into consideration/seeing the ENTIRE list of keywords per record. What I am envisioning is that there may be more than one record which may have "60 watt" - ex:

Bulb
====
id ...
1  ...
2  ...

Keyword
=======
id description
1  60 watt
2  replacement
3  halogen

BulbKeyword
=============
Bulb_id  Keyword_id
1        1
1        2
2        1
2        3

notice that Keyword_id=1 appears for two different bulbs. Just as an example, imagine that during development (of the site you are working on) 60W is what is intendended and you build all the pages of your site, and all the pages that are supposed to have 60W are complete. Then right before "deployment" it turns out that it it should be 80W instead of 60W!

If you were to break the list above at the commas, you are basically left with a list of tokens (regardless of how many words within each token). Now the question: Is it guaranteed that each token will be used only once? If yes, then your one-to-one statement is true. Otherwise you should break it down as suggested. I hope this sheds some "light" [pun intended :)] in the matter.

[teedoff]

Ok I am understanding perfectly what you are saying now! lol One question arises now that I need to clear up.

Once I remove the "keyword" column from my rlbbulbs table. then create a child table called "bulbKeywords" as you suggest above.

Keyword
=======
id    description
1     60 watt
2     replacement
3     halogen

Then in my bulbs table I would have a foreign key column called keywordsID which would contain data something like this:

bulbTable
============
bulbID  bulbName   keywordID
1        GE        1,2,3
2        Sony      2,3
3        Osram     1,3
4        Eiko      1,2,3

In the keywordID column, potentially there could be 30 or 40 ID's there instead of just the 2 or 3 I have given as an example. I hope I understood this part as well. Thanks again!

[hielo]

I understood what you wrote, however, keywordID may NOT be a column bulbTable. I think it is clear now that 1 "represents" '60 Watt' but what if I were to enter the ID zero as a value of keywordID! In reality, the column keywordID should have ONLY numeric values that exist in the id column of Keyword table. The DB itself can enforce this "Requirement" but you can only have one value per record. So imagine that your "bulbTable" SHOULD be like this instead:

bulbTable
============
bulbID  bulbName   keywordID
1        GE        1
1        GE        2
1        GE        3
2        Sony      2
2        Sony      3
3        Osram     1
3        Osram     3

However, since bulbID is unique, the db will NOT allow it. So what you need to do is use a third table that will contain the (many-to-many) relationship:

bulbTable
bulbID buldName
1     GE
2     Sony
3     Osram

bulbKeywordTable
bulbID  keywordID
1       1
1       2
1       3
2       2
2       3
3       1
3       2

Look up articles pertaining to db data normalization. IF you go to http://en.wikipedia.org/wiki/Database_normalization, under "Normal Forms" you will find a table with each of the normal forms - each of which is a link to an article. Read them all.