Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First, a few responses to small points, to keep you on the straight and narrow path of Relational databases and db design.</p> <ol> <li><p>The whole idea is to place as many of the Rules right in the database, in one place, and <em>not</em> in code. Almost everything can be done via DDL: FK constraints; <code>CHECK</code> constraints; and <code>RULES</code> (all ISO/IEC/ANSI SQL requirements). Then all the users (your app is an user) can see all the rules and understand the database better. That protects the db, no matter what client is used to execute the code. Db vendors (that means commercial, not freeware) implementation of these constraints are more reliable than code.</p></li> <li><p>The requirement (not convention) for inserting rows to a child table is that the parent row must exist first. That is what the FK constraint does, it ensures the parent row exists. In a many-to-many table, both parent rows must exist before the child (with two FKs, one to each parent) can be inserted.</p></li> <li><p><code>types_id</code> is a horrible idea because you have broken design rules, and removed the possiblity of RI. Better to have separate columns with RI (FK constraints to each parent). (But there is an even better way.)</p></li> <li><p>All your<code>Id</code> columns, the PKs, should be renamed <code>TableId</code>. Each should have Private DataType of the same name. The column name is used unchanged wherever it exists, as an FK. The only exception is where you have two FKs to the same parent table: there it should be <code>RoleTableId</code>.</p></li> </ol> <p><em>What would be the best way to tackle this problem?</em></p> <p>Normalise. And you will have issues that are exposed, which you need to resolve. Therefore Normalise again. And keep doing that until you have no issues to resolve.</p> <ol> <li><p>Your single Message table is already half way there. You have intuitively Normalised the two tables into one. But there are issues to resolve, so let's handle them.</p> <ul> <li>Sebastian has provided the two many-to-many tables, so I won't repeat.<br> .</li> </ul></li> <li><p>Before you decide that that is final (and therefore the two many-to-many tables are final), I suggest you Normalise <code>Wall</code> and <code>Media</code>. To me, it looks like there are many common columns. If you Normalise that, you will get one table. Since it is a Thing that is exposed or furnished by a <code>Person</code> for the purpose of inviting <code>Messages</code>, and the type can be<code>{ Photo | Album | Mailbox | Wall }</code>, I would call it <code>PersonFurniture</code> or <code>PersonObject</code>.</p> <ul> <li>If that ends up as one table, then you won't need two many-to-many tables, just one.</li> </ul></li> </ol> <p><strong>Responses to Comments</strong></p> <ol> <li>It is easier and faster to draw the model, than to type long discussions. I have thought about most of your questions. Please check this and ask specific questions about anything you do not understand. </li> </ol> <p><a href="http://www.softwaregems.com.au/Documents/Student%20Resolutions/SONewbie%20DM.pdf" rel="nofollow"><strong>Link to Social Network Data Model</strong></a> (Page 3)</p> <p><a href="http://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf" rel="nofollow"><strong>Link to IDEF1X Notation</strong></a> for those who are unfamiliar with the Relational Modelling Standard.</p> <ul> <li>CHoose your own table and column names</li> <li><code>Message.Subject</code> can be set to <code>CHAR(0)</code> or ignored, if it is not Email.</li> <li>that <code>wall_message</code> and <code>email_message</code> are identical is <em>not</em> a problem, I've Normalised them into one table</li> <li>whether it is a <code>wall_message</code> or <code>email_message</code> or <code>media_message</code> is a matter of where it is "sent", right ? You can easily disallow any function (eg. grouping) for any message type via a CHECK constraint.</li> <li>you haven't answered (2) above</li> <li>I think message grouping is different from media grouping: think about when a photo album has a list of messages on it.</li> <li>nothing is a problem, the whole idea of modelling is, paper is cheap; the whole idea of Relational dbs is, to do as much as possible using constraints, checks, rules. If anything is wrong we can change it.</li> </ul> <p>(Do you want Race (3 levels) or 2 levels in your Ethnicity question ?)</p>
    singulars
    1. This table or related slice is empty.
    1. PODatabase Modeling: Facebook like messages
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPerformanceDBA
    1. USPerformanceDBA
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODatabase Modeling: Facebook like messages
      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. COsee my "Edit 1". I've incorporated changes based on my understanding of the suggestions here so far. I'm not sure how I can further normalize `Wall` and `Media`. They seems normalized to me.
      singulars
      1. PO
      1. USStackOverflowNewbie
    2. COalso, three questions: 1.) Is there any problem with message->subject (since it is only applicable to emails)? 2.) `wall_message` and `email_message` are identical; is this a problem? 3.) Only `wall_message` and `email_message` have this concept of "grouping" (and thus needs parent_message_id); media_message does need it as it is grouped by media_id. Is there a problem with this?
      singulars
      1. PO
      1. USStackOverflowNewbie
    3. CO@SONewbie. Answered in my post.
      singulars
      1. PO
      1. USPerformanceDBA
 

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