Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts

    Should we use multiple tables, or one big one??

    I have to relate "attachments" (images and various document types) to a bunch of different areas of work-flow.
    IE: Product records can have attachments, so can inventory records. Same with SalesOrders and SalesOrderItems, PurchaseOrders and PurchaseOrderItems, ... etc.
    All in all, there are 13 tables which need to have relationships defined.

    Now, my question is whether it would be better to have a relationship table for each are in the work-flow, or have one big table with all the relationship info?
    My top concerns are performance and maintenance ... performance being the primary.
    If I went with 13 relationship tables, that would be a bit more maintenance, however, those tables would be fairly lean and easy to maintain. I'm wondering whether a single table would be a (potential) bottleneck if there were dozens of select queries being run against it at a time.

    Thoughts?
    Experiences?
    General pearls of wisdom or sage advice?

    Thanks-a-bunch,
    ~ Mo
    Last edited by mOrloff; 06-11-2011 at 02:28 AM.
    ...because it's dundant already.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Not even clear how you would design a single table.

    For each image/doc would you have one row with many foreign keys into the various tables? With NULLs for the foreign keys that don't apply for this particular image/doc?

    That would work, but I don't think it's considered good design.

    Multiple use of read-only (or even read-mostly) tables in MySQL is never an issue. I don't see this kind of table ever causing a bottleneck. Since a single table would eat less memory (buffered tables, that is) than several small one, it would probably actually be *more* efficient.

    Ehhh...good DB design or no, I might be tempted to go with it.

    I think maybe maintenance should be the deciding factor.

  • #3
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Thanks.
    I think I'm just going with the single-table route then

    My thoughts on the table was just to have a single row for each attachment, and have a single column for all the FK's.
    Then, I could go one of two ways.
    - either have a TYPE column for a token code (ie: 'SO' for SalesOrders, 'SI' for SalesOrderItems, etc...)
    or
    - have a 13 boolean columns for each of the types (ie: isSalesOrder, isSalesOrderItem, etc...)

    What thoughts do you have? Do you see any benefits on one idea over the others??
    ~ Mo
    ...because it's dundant already.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    How can you have "a single column for all the FKs" if a given attachment is used by more than one other table? Won't the FK values be different for each table?

    Or are you saying that a given attachment can *NEVER* be applied to more than one other table??

    If the latter is the case, then I'd go with a single "type" column.

    But if it's ever possible for an attachment to be used by more than one other table, then I think you have it bass ackwards.

    I think that your "attachment" table should have 13 foreign keys.

    So now you know *WHICH* record in each of those 13 tables the attachment is related to!
    Last edited by Old Pedant; 06-14-2011 at 03:15 AM.

  • #5
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Old Pedant View Post
    ... are you saying that a given attachment can *NEVER* be applied to more than one other table?? ...
    Hmmmm ... definitely worth digging into.
    Off the top of my head, I'd say "yeah, never", but I'll do a little more research.
    Regardless of what the dept heads say, thanks to you, I now have a direction.

    Thanks-a-bunch (again)
    ~ Mo
    ...because it's dundant already.


  •  

    Posting Permissions

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