SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Ireland
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mySQL table organisation

    Any suggestions on how I would organise tables for this database:

    1. 100,000 book titles in existence;
    2. 5,000 shops sell some of these books;
    3. Shops can charge different prices for the same book.

    It seems simple but I am having difficulty with table size. For instance, if each shop had 1000 books, the no of rows in the table could be 5,000 x 1000 = 5,000,000 !! Is there a better way?

    Would I be better off having a separate text file for each shop and book and then link them together in some way, can webservers hold such large volumes of text files?

    Willie

  2. #2
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Database:
    • table Books
          - bookID
          - title
          - author
          - more info if you need it
    • table Shops
          - shopID
          - name of shop
          - more info if you need it
    • table books_$shopID (make a new table for every shop)
          - bookID
          - price
          - more info if you need it

    This is what I'd do, at least.
    Last edited by delemtri; Jul 20, 2001 at 10:37.

  3. #3
    You talkin to me? Anarchos's Avatar
    Join Date
    Oct 2000
    Location
    Austin, TX
    Posts
    1,438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should never base a table name upon data. Instead of table books_$shopID, it should be table prices with shopId as a column.

  4. #4
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, yes, you are right there.

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Ireland
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But wouldn't that number of tables be huge for a database?

    Any views on the flatfile approach?

    Willie

  6. #6
    You talkin to me? Anarchos's Avatar
    Join Date
    Oct 2000
    Location
    Austin, TX
    Posts
    1,438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, that wouldn't be huge at all, and it would be much better than a flatfile solution.

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Ireland
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any idea on the physical restrictions of a database as in :

    -Max no if tables (millions!!)
    -Max no of records in a table

    Also, why do you feel that flat files (one for each shop and book) would not work particularly if you had one index flatfile that would point to the relevant shop and book flatfiles. Could a script actually be more efficient in retrieving and updating data?

    Thanks for your help.

    Willie

  8. #8
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I may be wrong, but I think that if you/your host does not set a limit the maximum number of tables depends on the amount of free space on the server. This is because all the data is storred in files.

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  9. #9
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The schema presented so far looks nice to my

    The max table size (for MySQL anyway) is the max file size on your platform, as seanf mentions. On a 32bit platform (such as Linux on Intel) this is somewhere around 2GIG (I think, not certain ). But even then there is a patch you can apply to Linux to get around this.

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2000
    Location
    Louisville, KY, US
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency.

    For more info visit: http://support.microsoft.com/support.../q100/1/39.asp

    HTH
    EMB
    So never mind the darknes, we still can find the way. Cause nothin' last forever, even cold November rain.(Rose)

  11. #11
    One website at a time mmj's Avatar
    Join Date
    Feb 2001
    Location
    Melbourne Australia
    Posts
    6,282
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by delemtri
    Database:
    • table Books
          - bookID
          - title
          - author
          - more info if you need it
    • table Shops
          - shopID
          - name of shop
          - more info if you need it
    • table books_$shopID (make a new table for every shop)
          - bookID
          - price
          - more info if you need it

    This is what I'd do, at least.
    What you need, in my opinion, is to use a simple many-to-many database design.

    Here's my spin:

    PS: I understand that this is the same idea as Anarchos' - but I just wanted to describe it more fully, partially for my own understanding

    table books:
    - bookID
    - book_title
    - book_author
    - etc

    table shops
    - shopID
    - shop_name
    - shop_address
    - etc

    table items
    - shopID
    - bookID
    - item_price

    The table items only contains numerical values so it can be really fast. And the 5 million entries in this table should be fine.

    Because it is a many-to-many, there may be multiple occurences of the same shopID and/or multiple occurrences of the same bookID in the items table. This is normal.

    You should create non-unique indexes on the third table to help speed up queries - depending on how you are going to access it.
    Last edited by mmj; Jul 21, 2001 at 02:18.
    [mmj] My magic jigsaw
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The Bit Depth Blog Twitter Contact me
    Neon Javascript Framework Jokes Android stuff

  12. #12
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    umm, I don't think I read the earlier posts closely enough. What & Anarchos MMJ said, IMHO regarding needing many to many schema.
    Last edited by freakysid; Jul 21, 2001 at 10:03.

  13. #13
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But books are going to be in more than one shop.

    You could do something like having shopID in items equaling $shopID[1],$shopID[2],$shopID[3],... $shopID[$n], and then explode(",", $queryresultsfromselectstatement).


Bookmarks

Posting Permissions

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