| Author |
Topic |
|
iancuct
Yak Posting Veteran
73 Posts |
 Posted - 2003-02-06 : 12:06:16
|
| I want to make a table of categories fore exampleTable CategoryID Name Property1 Name1 102 Name2 203 Name3 304 Other 40Where Other can be multiple things. For example one can specify other as a property of 40 or as a property of 55its kind of weird ID Name Property1 Property24 Other 40 $1004 Other 55 $100where the key is the ID or the name one or the otherwhat is better? 1 to make a new table for other2 to make a list of categories names as a lookup table to the list of categories with the additional properties? |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-06 : 12:09:01
|
| Use a normalized design. If the cardinality of category to property is 0:M, follow the rules of normalization to put property in a seperate table.Jay White{0} |
 |
|
|
iancuct
Yak Posting Veteran
73 Posts |
Posted - 2003-02-06 : 12:40:43
|
| well the question is normalize it or just add a new field to the existing table |
|
|
|
iancuct
Yak Posting Veteran
73 Posts |
Posted - 2003-02-06 : 14:17:44
|
| well the question is normalize it or just add a new field to the existing table |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-02-06 : 14:52:11
|
| Adding fields is bad, M'kay?Normalization is the way to go.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
iancuct
Yak Posting Veteran
73 Posts |
Posted - 2003-02-06 : 15:42:49
|
| i understand or maybe i don'tbut lets say you have a simple list of fruitsapplesorangesprunes....otherone of the fruits is other. but the fact is that other can be pears or it can be bananas.Now i can put otherID linked to otherTable or i can make the key longer so that i can have multiple others, but i can't use one of the existing properties because they have to be different for each fruit (lets say) , so i have to add another property or another name to make the key longer.what i don't know is which way should i do it |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-06 : 15:53:44
|
| OK, Lets say you have a table that tracks the fruit you sell:CREATE TABLE Fruit_Trans (Trans_Id Int, Quantity Int, Fruit_Id Int)The reason you use an RDBMS is protect the integrity of you data. The Fruit_Trans table would then have a Parent. The Fruit_Code Table.It might Look Like.CREATE TABLE Fruity_Code (Fruit_Id Int, Fruit_Desc Varchar(255)You would then establish Referential Integity between the two tables by making Fruit_Id in Fruit_Trans a foreign key to Fruit_Code. This will Prevent anyone entering a transaction about a fruit you know nothing about.(You can also make Fruit Id a char/varchar and use the actual fruit name as your key. This prevents the need to join to the code table unless you need to get other information about the fruit, ie unit cost for example).Now you say, but I have to enter a new fruit, and this can be handled procedurally. In your s/p that would insert a fruit transaction, check for the existance of the fruit in the code table, if it's not there, either return an output variable, or a return code that tells you top, lets say pop a fruit entry screen so that the new fruit can be entered.OK...Now with all of THAT Said, there still is know way to prevent you from putting 'OTHER' or some other bogus data in the code table (well except with a validation rule of course).Hope this diatribe helps.Good LuckBrett8-) |
|
|
|
iancuct
Yak Posting Veteran
73 Posts |
Posted - 2003-02-06 : 17:47:44
|
| ok thanks, well i got it figured out. I had to change an old table that had the category names as a keyto the same table but with an ID this way itwill allow for more multiple othersfor exampleFruitID Fruit Name1 other2 otherThe requirements that i initially had were unclear and so i thought i had to have multiple other categories based on the name as a key. |
|
|
|
|
Best way to have multiple Other category