Results 1 to 13 of 13

Thread: Database design

  1. #1

    Database design

    Hello all.

    I'm designing a Relational Database for a jewellery designer.

    This is what i've got so far.

    (Sorry no diagrams yet i'm still thinking about the correct way)

    cms_item
    Code:
    item_id
    item_ref
    item_materials
    item_dscpn
    item_image_url
    cms_type
    Code:
    type_id
    type_name
    (entered data for cms_type)
    1 Necklace
    2 Bracelet
    3 Earring
    4 Ring
    5 Charms
    6 Brooch


    cms_item_type(joining table for cms _item and cms_type)
    Code:
    item_id
    type_id
    Ok so, so far that makes sense to me.

    Now comes a part that i can't seem to work out with my non logical brain.

    I need to put some of the items into sets

    So a set might have in it(only one of each item can be included in the set)
    1 necklace
    1 bracelet
    1 earings

    What would be the best way to join the sets up in the database?

    Would it be adding another field into cms_items that you add a number into thats unique to the group set.

    for example if you have a necklace and its in a set with an earing they would both have a set number say 1

    Or would it be having a table that you would have all the items ids in?

  2. #2
    hello,
    I hope i have some helpfull comments:

    First in a table like item i would define rows like this
    id, ref etc
    in a linking table with id from item table would be itemid
    you already know that the id is of item when you are looking for an id inthis table so putting item in front off it is redundant.

    then: can one item have more types?
    no: then you're typeid should not be in a linking table but rather in the item table

    for the sets you should use a similar structure as you used for the type and item link table

    table set has id

    table set_items has multiple setid, itemid 's

    hope it helps....
    Last edited by borrob; February 11th, 2007 at 10:59 AM.

  3. #3
    Thanks for comments.

    I don't think the naming of the fields has anything to do with this. (its of personal preference and wont slow the system down)

    Each item can't have more then one type.
    As each item is of a specific type. Eg a necklace can't be an earing and so on.

    If i did put the item type into the item table then it would mean repeating the data many times.

    But what i want to do is put the items in groups or sets that can be used to search.
    From what you saying it doesn't make sense.
    Last edited by deletedUser2352352; February 11th, 2007 at 11:13 AM. Reason: Made a change

  4. #4
    Ok you didn't grasp the idea quite fully...


    The naming is indeed your own prefferance.

    but about the type you still would have the type table but the typeid would be in the item table and not in the linking table item_type

    cheers

    and for the sets:

    set table
    id name
    1 all_in_one
    2 head_gear

    in the linking table set_items there would be
    setid, itemid
    1,1
    1,2
    1,3
    so set 1 would have item 1,2,3
    setid, itemid
    2,6
    2,4
    so set 2 would have item 6,4

    NOW YOU CAN SEARCH THE ITEMS THAT BELONG TO A SPECIFIC SET
    Last edited by borrob; February 11th, 2007 at 11:29 AM.

  5. #5
    foodpk's Avatar
    1,335
    posts
    ask me about being a jerk
    In your case, you just have to add another table for a set and then add a foreign key field into the cms_item table. That way, one set has many items, but one item belongs to only one set. I've made a diagram that might help you, at least I think that's how you imagined it.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	neki.png 
Views:	14 
Size:	8.7 KB 
ID:	41119  

  6. #6
    Cheers Foodpk
    Thats what i was thinking.

    Something like this?

    cms_items
    Code:
    item_id (pk)
    item_ref
    item_materials
    item_dscpn
    item_image_url
    item_set (fk)
    cms_sets
    Code:
    set_id (pk)
    set_name
    set_dscpn
    borrob
    Thanks for your advice it makes sense now.
    Cheers.
    Last edited by deletedUser2352352; February 11th, 2007 at 11:37 AM. Reason: explained myself more

  7. #7
    it all depends on what you want exactly,

    in my example one item can belong to multiple sets
    in the other example one item can belong to only one set.....

    and for the type, that's pure logic.
    if your item can only have one type then you shouldn't make it possibble to link more types....

    cheers

  8. #8

  9. #9
    there are some design isues here:

    Still you could assign multiple types to one item ( that's not logical )
    The foriegn_key in for_sale makes a cascaded delete impossible so you cannot define a foreign_id with cascaded delete in for example an innoDB in mysql

    so rather i would have in the for_sale items only sets and for every item i would define at least a set with only that item. so the cascaded delete works and you can forget about the is_item and is_set ....


    cheers

  10. #10
    ok Borrob

    I like your thinking.

    After talking to the designer.

    One item may belong to many types and many sets.

    So for example

    a necklace may also be able to be used as a bracelet.

    So from what your saying that part of my database is right.

    And from what you said that necklace may also be in many sets.

    So from what everyone is saying i could do it like this.

    cms_items
    Code:
    item_id (pk)
    item_ref
    item_materials
    item_dscpn
    item_image_url
    cms_type
    Code:
    type_id(pk)
    type_name
    cms_item_type(joining table for cms _item and cms_type)
    Code:
    item_id(fk)
    type_id(fk)
    (allowing items to grouped under different types)

    cms_sets
    Code:
    set_id (pk)
    set_name
    set_dscpn
    cms_item_sets(joining table for cms _item and cms_set)
    Code:
    item_id(fk)
    set_id(fk)
    (allowing items to grouped under different sets)

    Is that right?
    Last edited by deletedUser2352352; February 11th, 2007 at 12:03 PM.

  11. #11
    my design does everything you need. it also allows items to be sold separately from sets if you wish (via the binary is_item and is_set columns in cms_for_sale). even though mine does prevent a cascade delete, it's trivial to add that functionality into your procedures if you wish. not to mention, if i were building this, i wouldn't give anyone delete privileges at all. i would use an is_deleted (binary) column which could be updated.

  12. #12
    cheers bwh2

    Makes sense.

  13. #13
    also, you could easily add a is_for_sale (binary) column to your cms_items and cms_set table in place of the cms_for_sale table.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012