single or multiple tables

 

Should this be represented in the database as 1 table or 3 tables? I and my friend have different opinions about this so I'd like to see the general views on this. (Maybe it should be a vote for either solution?) 

Create Table Order
// Basic fields of the table
 - ID (Primary key)
 - CustomerID  (integer, with a FK)
 - Quantity
 - ProductID  (integer, with a FK)

 // Then depending on user selection, either these fields need to be specified 
 // (could be factored out to a separate table):
 {
 - InternalAccountID (integer, with a FK)
 - InternalCompanyID (integer, with a FK)
 }

 // Or these (could be factored out to a separate table):
 {
 - ExternalAccountNumber (free text string)
 - ExternalCompanyName (free text string)
 - ExtraInformation (free text string)
 }

1 table approach:

Pros:

  • performance (one insert as opposed to two, FK check, no joins)
  • probably takes less space (the extra tables have overhead + indexes + extra ID field)
  • one table as opposed to three
  • hardly justifiable to have split out to new tables just for 2+3 fields (or what?)

    • Cons:

  • Nullable fields
  • Potentially extra "type" column (can be skipped)
  • Breaks 3NF (?)

    • Pros and cons kindly requested as well as personal opinions. :)

    EDIT: I tried simplifying the example by using different entities than I am actually using so any suggestions to altering the model wouldn't really help me. Ie focus on the technical aspects more than the domain model please.

    My opinion would be that if 

     // Then depending on user selection, either these fields need to be specified 
 // (could be factored out to a separate table):
 {
 - InternalAccountID (integer, with a FK)
 - InternalCompanyID (integer, with a FK)
 }

 // Or these (could be factored out to a separate table):
 {
 - ExternalAccountNumber (free text string)
 - ExternalCompanyName (free text string)
 - ExtraInformation (free text string)
 }

    are always 1:1 with an order (ie, you can't have 3 accountIDs), then leave it as one table. To take care of your null issue, you could add one more column called InternalCustomer (boolean) or CustomerType (varChar) that you could use to define an internal or external customer to know which of the two sets of fields you should look at for a specific customer.

    Since we don't know the full use of this data or the schema for the entire DB, any response on this can't really be fully qualified.

    Hope this is self-explanatory.

    order_model_v1

    If you want to avoid data duplication, you should go with a 2- or 3-table solution. For example, if you have the External columns in the Order table, value could exist multiple times. If the data looks like this: 

    ID   ExternalCompanyName
1    ACME
2    ACME
3    My Company
4    ACME

    Now, if ACME changes names to ACME, Inc. you must update many rows. If the tables are normalized, where external companies are in a separate table, you would update one row. Note, there may be an argument to put Account Number in it's own table, but we'll leave that for extreme normalization.

    It doesn't appear to be a 1-to-1 relationship between an order and a company/account, unless each company/account can only have one order. it sounds more like a 1-to-many relationship.

    Now, what happens if a mistake is made when updating the ExternalCompanyName in a single-table environment, and only some of the rows get updated. You have some rows with ACME and some rows with ACME, Inc. You end up with a bad-data situation.

    Also, if this is really a 1-to-many relationship, you really aren't saving space. You are duplicating data in the order, rather than storing it once in another table.

    链接地址: http://www.djcxy.com/p/70610.html

    上一篇: ERD。 建模问题

    下一篇: 单个或多个表