Databases
StackOverflow2013
Postsdbo
POConflicting desires in Database Design, with fields of two similar functions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POConflicting desires in Database Design, with fields of two similar functions
    primarykey
    data
    text
    <p>Okay, so I'm making a table right now for "Box Items".</p> <p>Now, a Box Item, depending on what it's being used for/the status of the item, may end up being related to a "Shipping" box or a "Returns" box.</p> <p>A Box Item may be defective:if it is, a flag will be set in the Box Item's row (IsDefective), and the Box Item will be put in a "Returns" box (with other items to be returned to that vendor). Otherwise, the Box Item will eventually be put into a "Shipping" box (with other items to be shipped). (Note that Shipping and Returns boxes have their own tables: there's not one common table for all boxes... though maybe I should consider doing that if possible as a third possibility?)</p> <p>Maybe I'm just not thinking clearly today, but I started questioning what should be done in this situation.</p> <p>My gut tells me that I should have a separate field for each possible relation, even if only one of the relations can happen at any given time, which would make the schema for Box Items look like:</p> <p>BoxItemID Description IsDefective ShippingBoxID ReturnBoxID etc...</p> <p>This would make the relations clear, but it seems wasteful (since only one of the relations will be used at any time). So then I thought I could have just one field for the BoxID, and determine which BoxID it's referring to (a Shipping or a Returns Box ID) based on the IsDefective field:</p> <p>BoxItemID Description IsDefective BoxID etc...</p> <p>This seems less wasteful, but doesn't sit right with me. The relation isn't obvious.</p> <p>So, I put it to you, database gurus of Stackoverflow. What would you do in this situation?</p> <p>EDIT: Thank you everyone for your input! It's given me a lot to think about. For one, I'm going to use an ORM next time I start a project like this. =) For two, since I'm not right now, I'll bite the four bytes and use two fields.</p> <p>Thanks everyone again!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USEdgarVerona
    1. USEdgarVerona
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POConflicting desires in Database Design, with fields of two similar functions
      1. USCed-le-pingouin
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload