Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It depends on whether the person-to-address relationship is one-to-zero-plus or one-to-one-plus.</p> <p>If a person is required to have a primary address, I would put it in the <code>Persons</code> table itself (since it's a required attribute).</p> <hr> <p>In the other hand, if a person can exist in your schema without an address, I would leave all addresses in the <code>Addresses</code> table as equal and use an attribute of the <code>Persons</code> table to select the primary (either NULL or a pointer to the relevant <code>Addresses</code> row).</p> <p>If you store the primality of an address in the <code>Addresses</code> table, what do you do when two addresses for Bob Smith both claim to be the primary? You could stop that with triggers but it's far more efficient to design your schema properly.</p> <p>And, if two room-mates share the same address, but one lives there all the time and the other spends most of his time shacked up with his girlfriend, what happens then? If the primality is in the Addresses table, you won't be able to share address rows between persons.</p> <hr> <p>What I'm trying to get across is that you need to allocate your attributes to the right objects. A person's primary address belongs to a person, not an address.</p> <p>For maximum efficiency and flexibility, I would have the following schema:</p> <pre><code>Persons: Id primary key PrimaryAddressId OtherStuff Addresses: Id primary key OtherStuff PersonAddresses: Id primary key PersonId foreign key on Persons(Id) AddressId foreign key on Addresses(Id) </code></pre> <p>You have the minor data integrity problem that <code>Persons.PrimaryAddressId</code> may be a hanging pointer. You can't make it a foreign key to one of the primary keys since you want it to allow <code>NULL</code>. That means you'll have to cater for the possibility that it might point to a non-existent <code>Addresses.Id</code>.</p> <p>I would simply fix that as a before-delete trigger on <code>Addresses</code> so that the relevant <code>Persons</code> rows are updated (setting <code>PrimaryAddressid</code> to NULL).</p> <p>Or you could be tricky and have one address of "Unknown" in the <code>Addresses</code> table so that every row in <code>Persons</code> has at least one address (those whose primary address is unknown automatically get their <code>PrimaryAddressid</code> set to the "Unknown" address row.</p> <p>Then you could make it a proper constrained relationship and simplify your SQL somewhat. Pragmatism often beats dogmatism in the real world :-)</p>
    singulars
    1. This table or related slice is empty.
    1. POWhat is the best schema design for child collection with a "primary" entity
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USpaxdiablo
    1. USpaxdiablo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhat is the best schema design for child collection with a "primary" entity
      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. VTAcceptedByOriginator
    1. COWould you still use this design if the Persons->Addresses relationship was constrained to one-to-many, as opposed to the man-to-many this permits? In that case, there may be occasional duplicate Addresses, but in your roommate scenario, for instance, if one roommate moves out you do not want to change the Address for both roommates.
      singulars
      1. PO
      1. USerg39
    2. COIf it's truly one-to-many, I'd rethink it. But I don't believe this particular situation warrants it. Making this one-to-many duplicates data in the database unnecessarily. Re the moving situation, you wouldn't update the addresses row for the science geek staying behind, you'd simply change the computer nerds primary to point to a new address record, already existing if his girlfriend is on-campus or just add a new one if she goes to the cheerleading school up the road :-)
      singulars
      1. PO
      1. USpaxdiablo
    3. COTechnically there shouldn't be duplicate rows in a table at all. An address is an object and all attributes for that object should be in it's single row. What happens when you have two rows for 7 Smith Street and they have different ideas about the address (like rent to charge, or rooms left to rent)? DBAs travel the globe trying to remove problems like these. Don't make me come over there :-)
      singulars
      1. PO
      1. USpaxdiablo
 

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