Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    need suggestions for a simple inventory DB design

    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?

  2. #2
    Join Date
    Sep 2004
    Posts
    4
    Quote Originally Posted by mypatpat
    subcatagory(id, c_id, name), c_id references catagory(id)
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2004
    Posts
    4
    Quote Originally Posted by r937
    many people would put the subcategories into the same table along with the categories
    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?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2004
    Posts
    4
    I see. I guess I over analyized the problem and forgot that it is natually a tree structure...

    Thanks for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •