Results 1 to 7 of 7

Thread: Database Design

  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Database Design

    I have a table to store the information for an item
    1 Sound Card
    2 Graphic Card
    3 Monitor


    For each item , there are five shops that i can distribute this item to.

    id itemID shopID status
    1 1 1 0
    2 1 2 1
    3 1 3 1
    4 1 4 1
    5 1 5 0
    6 2 1 0
    7 2 2 1
    8 2 3 1
    9 2 4 1
    10 2 5 0

    Other then using this way is there any other simpler way to keep track of this record. i only hva 3 items and the record already has 15 rows. If i hv one thousand items that means the size of the table going to grow till 5000 rows.As for the status 1 indicates that the item will be distributed to them.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your design is fine

    thousands of rows is still considered small

    millions of rows might be a medium-sized table, billions of rows would be large
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Posts
    4
    I just have anothe few more question

    will the above method be better or combining the shop id in one field is better ??

    eg
    Item id Item Name Shop ID
    1. sound card 2,3,4
    2. graphic card 2,3,4

    or

    I should only put shops that is choosen only which i can eliminate the status field. This means that when ever user make changes, user input needs to check wif the database to see if the record exist. If it does then record wont be added or else record will be added.

    id itemID shopID
    1 1 2
    2 1 3
    3 1 4
    4 2 2
    5 2 3
    6 2 4

    Just wanted to know what is the most efficient way for me to retrieve and store the data. Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    2nd way is much better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Posts
    4
    do u mean this way ??

    eg
    Item id Item Name Shop ID
    1. sound card 2,3,4
    2. graphic card 2,3,4

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no no no!

    this way --

    itemID shopID
    1 2
    1 3
    1 4
    2 2
    2 3
    2 4

    notice you do not want to have an auto_increment in this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Winsonlee

    (Starting with your initial post)

    If the first table is Item, and the next table is Distribution, you need a Shop table (second) before Distribution.

    Shop will have shopID as the PK.

    In the Distribution table, the PK is shopID plus itemID. You do not need id.

    The numbers you are discussing are tiny. In any case, the no of rows is not a relevant consideration in database (table) design. Designing a database properly will reduce future maintenance effort (ie. when the inevitable extensions, enhancements, etc are implemented).
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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