StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Generally speaking to design a database you create a table for each object you will be dealing with. In you example you have <code>Users</code>, <code>Posts</code>, <code>Comments</code> and <code>Media</code>. From that you can flesh out what it is you want to store for each object. Each item you want to store is a field in the table:</p> <pre><code>[Users] ID Username PasswordHash Age Birthdate Email JoinDate LastLogin [Posts] ID UserID Title Content CreateDate PostedDate [Comments] ID PostID UserID Content [Media] ID Title Description FileURI </code></pre> <p>Taking a look above you can see a basic structure for holding the information for each object. By the field names you can even tell the relationships between the objects. That is a post has a <code>UserID</code> so the post was created by that user. the comments have a <code>PostID</code> and a <code>UserID</code> so you can see that a comment was written by a person for a specific post.</p> <p>Once you have the general fields identified you can look at some other aspects of the design. For example right now the <code>Email</code> field under the Users table means that a user can have one (1) email address, no more. You can solve this one of two ways... add more email fields (EmailA, EmailB, EmailC) this generally works if you know there are specific types of emails you are dealing with, for example EmailWork or EmailHome. This doesn't work if you do not know how many emails in total there will be. To solve this you can pull the emails out into its own table:</p> <pre><code>[Users] ID Username PasswordHash Age Birthdate JoinDate LastLogin [Emails] ID UserID Email </code></pre> <p>Now you can have any number of emails for a single user. You can do this for just about any database you are trying to design. Take it in small steps and break your bigger objects into smaller ones as needed.</p> <p><em>Update</em></p> <p>To deal with friends you should think about the relationship you are dealing with. There is one (1) person with many friends. In relation to the tables above its one User to many Users. This can be done with a special table that hold no information other than the relationship you are looking for.</p> <pre><code>[Friends] [UserA] [UserB] </code></pre> <p>So if the current user's ID is in A his friend's ID is in B and visa-verse. This sets up the friendship so that if you are my friend, then I am your friend. There is no way for me to be your friend without you being mine. If you want to setup the ability for one way friendships you can setup the table like this:</p> <pre><code>[Friends] [UserID] [FriendID] </code></pre> <p>So If we are both friends with each other there would have to be 2 records, one for my friendship to you and one for your freindship to me.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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