Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>similar to Facebook's friends list. Each user will be able to view the profile of users and add them to their tribe. Each user is the chief of only one tribe</p> </blockquote> <p>Okay, so you have a <code>User</code> table, and also will need a <code>Tribe</code> table.</p> <p>Then the relations you're describing are a <code>chief-of</code>, which is one-to-one (one user can be chief of one tribe; one tribe has only one chief), therefore you can either store this within <code>User</code> (chief_of: Tribe) or within <code>Tribe</code> (chief: User).</p> <pre><code>CREATE TABLE User ... chief_of integer </code></pre> <p>Here, <code>chief_of</code> might be a foreign key so that if you delete a tribe, the relevant tuple will have its <code>chief_of</code> set to NULL (a user can't be chief of a no longer existing tribe).</p> <p>The membership is a bit more complicated because one user can belong to several tribes, and a tribe will have more than one member.</p> <p>This is a many-to-many relationship and is usually done with a table holding key pairs:</p> <pre><code>CREATE TABLE member_of ( user_id integer, tribe_id integer ); </code></pre> <p>Both fields are natural candidates for foreign keys. <a href="http://www.java2s.com/Tutorial/MySQL/0080__Table/Implementamanytomanymap.htm" rel="nofollow">Here</a> you can find a similar implementation using <code>Authors</code> and <code>Books</code>.</p> <p>To indicate that Bob is a member of the Clan of the Cave Bear, you retrieve the ids of Bob and Bears, and insert a tuple in <code>member_of</code>.</p> <p>To retrieve all members of the clan, you can use a JOIN:</p> <pre><code>SELECT Users.* FROM Users JOIN member_of ON (Users.user_id = member_of.user_id) WHERE member_of.tribe_id = (SELECT tribe_id FROM Tribes WHERE tribe_name = 'Clan of the Cave Bear'); </code></pre> <p>I think that a shorter version of that ON in MySQL is <code>USING(user_id)</code> (meaning that both tables have an identical column identically named), but in my opinion the <code>ON</code> is clearer.</p> <p>You can also retrieve a virtual "is_chief" column:</p> <pre><code>SELECT Users.*, chief_of = tribe_id AS is_chief FROM Users JOIN member_of ON (Users.user_id = member_of.user_id) WHERE member_of.tribe_id = (SELECT tribe_id FROM Tribes WHERE tribe_name = 'Clan of the Cave Bear'); </code></pre> <p>The one user whose <code>chief_of</code> attribute is equal to the tribe id will have <code>is_chief</code> set to TRUE, which is equal to 1, so </p> <pre><code>SELECT Users.*, chief_of = tribe_id AS is_chief FROM Users JOIN member_of ON (Users.user_id = member_of.user_id) WHERE member_of.tribe_id = (SELECT tribe_id FROM Tribes WHERE tribe_name = 'Clan of the Cave Bear') ORDER BY (chief_of = tribe_id) DESC, user_name; </code></pre> <p>will retrieve the users in alphabetical order, except the chief, who, if present, will come first.</p> <p>As for the acceptance into the tribe, this identifies three <em>states</em>: a user is not in a tribe, a user is in the tribe, a user <em>asked to be</em> in a tribe. The first two are actually two faces of the same attribute <code>member_of</code>. So naturally we might create a new attribute and call it <code>wants_in</code>. It would map to a table identical to <code>member_of</code>.</p> <p>A chief could retrieve all tuples of <code>wants_in</code> whose <code>tribe_id</code> is equal to his own <code>chief_of</code> (so if it's NULL, meaning he's <em>not</em> a chief, this will automatically return <em>nothing</em>). Then he might see this as a table of checkboxes with user names. When he approves the join, for each approval you delete the tuple from <code>wants_in</code> and put it into <code>member_of</code>.</p> <p>Or you might decide that "membership" is a state in itself, so that you have a more complex join table</p> <pre><code>user_id, tribe_id, status </code></pre> <p>where <code>status</code> could be, say,</p> <ul> <li>nothing (there's no (U, T, ?) tuple): user U and tribe T are unknown to each other</li> <li>100: user U is full member of tribe T</li> <li>-1 : tribe T has decided that U is not a member <em>and cannot even ask to be</em>.</li> <li>0: user U wants to be member of T</li> <li>1-99: user U is a probationary (apprentice) member.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POadding users to different groups
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USLSerni
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POadding users to different groups
      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
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWow. Thanks! I'll try and wrap my head around this.
      singulars
      1. PO
      1. USGeorge Pazdral
    2. COYou did not say whether you're using a framework, an ORM, and which one. If you do, all the above is probably already supplied and you only need to "inform" the framework that, e.g., `User` has a many-to-many "member" relationship, and a one-to-one "chief" relationship, with `Tribe`. The ORM will do the rest. You might be interested in checking out, e.g., Laravel.
      singulars
      1. PO
      1. USLSerni
    3. CO+1 Holy crap. I came back here like "WTF lost the accept?" but this is fantastic. One of those answers I wish I could +1 multiple times.
      singulars
      1. PO
      1. USBen
 

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