Hello guys, please criticize what I have here...

It's a simple inventory database with 4 tables:

catagory(id, name)

subcatagory(id, c_id, name), c_id references catagory(id)

supplier(id, name, address, ..... )

items(id, s_id, c_id, sc_id, name, price, ...), s_id references supplier(id), c_id references catagory(id), sc_id references subcatagory(id)

So, in my design, subcatagory and items are weak entities, as they are both not uniquely identifiable by only their primary key.

Any suggestions on this design? Should I keep my weak entities or eliminate them and add relationship tables instead?

One more thing, is it alright to have a foreign key (c_id in this case) as one of the primary keys? Since the id itself doesn't uniquely identify the name of the subcatagory, but id,c_id -> name

your design is fine

many people would put the subcategories into the same table along with the categories

also, the items should relate to the subcategory but not the category as well, so that you can "transfer" a subcategory from one category to a different category by manipulating the subcategory's foreign key to the category, and thereby not have to make any change to the items at all

Is this what you mean?

1 A A1
2 A A2
3 A A3
4 B B1
5 B B2
6 C C1
7 ......

Does this not generate redundant data?

no, like this --

id name parentid
1 animal NULL
2 vegetable NULL
3 mineral NULL
4 doggie 1
5 kittie 1
6 horsie 1
7 gerbil 1
8 birdie 1
9 carrot 2
10 tomato 2
11 potato 2
12 celery 2
13 rutabaga 2
14 quartz 3
15 feldspar 3
16 silica 3
17 gypsum 3
18 hunting 4
19 companion 4
20 herding 4
21 setter 18
22 pointer 18
23 terrier 18
24 poodle 19
25 chihuahua 19
26 shepherd 20
27 collie 20

this is called the adjacency model

see? no redundancy, and unlimited levels of subcategories, if you need them

I see. I guess I over analyized the problem and forgot that it is natually a tree structure...

Thanks for your help