help with my table design please

[rodchar]

hey all,
i have an expense table like followed:

Name,Date,Amt
-------------------
McDonald's,10/5/2007,3.99

i'd like to attach some type of category to it and here's what i have so far:

CategoryTable
CatID,Name,SCatID(?)
--------------
1,Food

SubCategoryTable
SCatID,Name,CatID
-----------------------
1,Restaurant,1
2,Grocery,1


Does this design look good so far? i'm thinking subcategory is what i'll
need to attach to the expense tabe, right? or does subcategory and category
tables need to be just one table?

thanks,
rodchar

 

[tina]

Does this design look good so far? i'm thinking subcategory is what i'll

need to attach to the expense tabe, right? or does subcategory and category
tables need to be just one table?

i'd say, cautiously, that it looks okay. and yes, you'll want to use the
SCatID field as a foreign key in tblExpenses, linking subcategories to
expenses in a one-to-many relationship. make sure you do *not* include a
foreign key from the categories table in tblExpenses; it's not needed there,
because categories are already directly linked to subcategories - and hence,
indirectly linked to expenses.

hth

 

[rodchar]

the more i think about it the better it sounds to make categories and
subcategories into just one table??

 

[tina]

what's your reasoning?

hth

the more i think about it the better it sounds to make categories and
subcategories into just one table??

 

[rodchar]

i don't know. i figure since it's hierarchical data this would be a good
candidate? i'm not sure??

 

[tina]

well, i'd say "the more i think about it the better it sounds" is not a
valid reason for making structural decisions. if you don't have a specific,
solid reason for the change, suggest you don't make it.

hth