Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Take an order with multiple order line items as an example. The weak entities would be the individual line items stored in their own table. Their primary key could be the primary key of the order, plus a simple integer number (e.g. 1, 2, 3, which is unique only within the order.) Thus, they don't really have their own primary key as a unique numbered column, their key spans two columns and is only unique that way.</p> <p>The order line items should be deleted if and when the order is deleted - they don't make sense standing on their own. It is this linkage that makes them weak -- one thing being deleted should delete the other.</p> <p>If you give each order line item their own primary key, you'll still need to relate them back to the order item, which means putting in a foreign key for the order item or, having a cross reference table. (You may also need to know the line item number from the order, which would mean adding a simple integer column... and at this point you've added enough to have a key without an auto generated one.) For the design pattern of owned sub items, either of these alternatives is a bit of overkill.</p> <p>Using the complex primary key also enforces the relationship between order and line order items, in that this schema will not allow you cannot have a line item assigned to multiple orders.</p> <p>Another consideration is that you can shard the orders and order line items according to the order item primary key, since both tables have that key. (Sharding is generally easier to do based on the primary key than regular columns.)</p> <hr> <p>Hierarchical containment isn’t always what you want; but, it is such a commonly occurring pattern that it is nice to be clear about it, and composite keys can be used in this case. Here, using order items with line items as sub-items (i.e. contained), we’re saying not just that line items are 1 to many with respect an order, but that line items are owned and don’t exist independently of orders — that line items compose to create a single order object.</p> <p>In keeping with that, we’re explicitly not going to manage a separate key space for (all) line items (together as a group), but instead borrow and extend the key space of an order. Instead of asking the system to maintain a separate key space for line items, and manually (i.e. less formally) maintaining a foreign key relation back to the order, and also maintaining an integer line item rather separately (from the order foreign reference), we can ask the system to ensure uniqueness of the whole composite key, which includes the line item number within the order.</p> <p>Of course, you wouldn’t be able to add a line item that isn’t associated with an order, but additionally, using the composite sub-key, you also won’t be able to add one that overlaps with another (e.g. it won’t let you add two line item #3’s for the same order).</p> <p>This forces producers and consumers of line items to think about them as being contained within and part of orders, and not as independent items, or, put another way, to reference a line item by going thru an order, or, yet in other words, to get a reference to the order “for free” by referencing one of its line items. (And because you also have a reference to the order as part of such a foreign key, you can also use that order portion of the composite foreign key alone to group or join.)</p>
    singulars
    1. This table or related slice is empty.
    1. POWhat could go wrong if one ignored weak entities during database modelling?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USErik Eidt
    1. USErik Eidt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhat could go wrong if one ignored weak entities during database modelling?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThank you very much for the answer. I'm glad you mentioned the **Orders** and **lines** example because I'm working on an ordering system that is modeled exactly like you explained. Both `Orders` and `lines` tables have a surrogate primary key which are `order_id` and `line_id` respectively. The `lines` table has a **non null-able** foreign key `fk_order_id` pointing to the `order_id` and is configured for cascade update and delete. I fail to see why in this case specifically using a composite key is better??
      singulars
      1. PO
      1. USSongo
    2. COUsing a composite key enforces the sub-item relationship, and announces it to both the dbms (e.g. for sharding) and to other users (clients who may store the key, including other tables). The former means better dbms scale out, and regarding the latter, use of composite key makes (other table’s) foreign keys for line items into implicit references connected to the owning order. This results in a stronger schema that is usually preferred for the sub-item pattern.
      singulars
      1. PO
      1. USErik Eidt
    3. CO"_use of composite key makes (other table’s) foreign keys for line items into implicit references connected to the owning order._" I'm not sure that I understood that part completely. Could you please update your **Order has lines** example to further elaborate on that point. I have a feeling that this is the point that I have been looking for.
      singulars
      1. PO
      1. USSongo
 

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