Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>By splitting the user in this way you have exactly 1 row in user per user, which links to 0-n rows each in user_tel, user_details, user_addr</p> <p>This in turn means that these can be considered optional, and/or each user may have more than one telephone number linked to them. All in all it's a more adaptable solution than hardcoding it so that users always have up to 1 address, up to 1 telephone number.</p> <p>The alternative method is to have i.e. <code>user.telephone1</code> <code>user.telephone2</code> etc., however this methodology goes against 3NF ( <a href="http://en.wikipedia.org/wiki/Third_normal_form" rel="nofollow">http://en.wikipedia.org/wiki/Third_normal_form</a> ) - essentially you are introducing a lot of columns to store the same piece of information</p> <p><strong>edit</strong> </p> <p>Based on the additional edit from OP, assuming that each user will have precisely 0 or 1 of each tel, address, details, and <strong>NEVER</strong> any more, then storing those pieces of information in separate tables is overkill. It would be more sensible to store within a single user table with columns user_id, username, tel_no, addr, details.</p> <p>If memory serves this is perfectly fine within 3NF though. You stated this is not about normal form, however if each piece of data is considered directly related to that specific user then it is fine to have it within the table.</p> <p>If you later expanded the table to have telephone1, telephone2 (for example) then that would violate 1NF. If you have duplicate fields (i.e. multiple users share an address, which is entirely plausible), then that violates 2NF which in turn violates 3NF</p> <p>This point about violating 2NF may well be why someone has done this.</p>
    singulars
    1. This table or related slice is empty.
    1. POSpreading/distributing an entity into multiple tables instead of a single on
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSimon at mso.net
    1. USSimon at mso.net
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSpreading/distributing an entity into multiple tables instead of a single on
      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. COI should have stated that it's not the 3rd normal form that needs to be taken care of, let's suppose this in itself is an entity and if you perform all of those joins, you end up with data that's perfectly 1-to-1 and redundancy free. I do agree, one use could have 0 or 1 tel no, that is NULL. So there's a mechanism in today's RDMSs out there that help dealing with this issue.
      singulars
      1. PO
      1. USteodron
    2. COYou asked for a scenario for this kind of database design so I gave you one. I'm not entirely sure what you are looking for at this point by way of response. If there will always be exactly one piece of data for each field, and no possibility of a second etc, then having the row as a column within the user table would suffice. A possible rationale could be to have a table of VARCHARs with numeric IDs which are then linked to a user via an n:m join table however situations where that would be advantageous are difficult to suggest. All in all your question is too vague as to your problem.
      singulars
      1. PO
      1. USSimon at mso.net
    3. COThank you, I tried to edit it a bit and get rid of some vagueness. My initial worries where to find scenarios other than the trivial ones where this approach makes sense. So, if NF3 isn't broken, it's easier and healthier to leave all of the entity attributes in the same table as columns and there's a rare chance of finding a scenario where "fissioning" these entities this way does justify a performance bonus. E.g. is SELECT attr1, attr2 FROM entity costly if entity has many attributes, but none of them is actually logically connected to any other (as to breach the FN3 rule!?). Thnx!
      singulars
      1. PO
      1. USteodron
 

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