SitePoint Sponsor |
|
User Tag List
Results 1 to 18 of 18
-
Jan 3, 2009, 17:59 #1
- Join Date
- Dec 2007
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Modeling and Designing a database
I'm trying to learn more about modeling and designing databases and I figured the best way would be to actually write some local apps for academic purposes only. The first app I'm going to write is going to be very simple, it's just going to store specific information for each specific user. The data I'm going to be storing is:
firstname
lastname
state - for a specific search, like find all users in California
email - for login
pass - for login
-------------
link1
link2
........ - the main "data" for each user, 10 hyperlinks to wherever.
link9
link10
-------------
My first question is, for something this simple, would having just one table be OK? Assume there could be lots of users and the DB might change down the road. Maybe the fictional boss wants users to have up to 25 links so an additional 15 links will need to be added to the table. Also, users might not actually provide 25 links (that's just their limit) so there could be a lot of null values being stored.
I've also broken this down into 3 possible tables (with this being the primary key) so this is my attempt at listing an entity relationship diagram here:
Users
- user_id
- firstname
- lastname
- pass
Links
- user_id
- link1
- link2
- ...
- link9
- link10
State
- user_id
- state
The cardinality I've come up with, which might be a bit hard to decipher is:
State 1-----(contains)-----M Users 1-----(has)-----M Links
1 State can have many Users, many Users can live in 1 State (Users -> State is M to 1 relationship).
1 User has many Links, many Links can belong to 1 user (Users -> Links is 1 to M relationship).
Does this look OK? Is this the better way to do this, even though it adds more complexity than the first example of just one table?
-
Jan 3, 2009, 18:40 #2
- Join Date
- Nov 2008
- Posts
- 172
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Your links table limits the users to how ever links you decide to put into the table. I would make your table like the following:
Code SQL:linkid auto INCREMENT username linkname
or something along those lines. So that you can group by username.
-
Jan 3, 2009, 18:58 #3
- Join Date
- Jul 2003
- Location
- Kent
- Posts
- 1,921
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
You need two tables, one for the user (and state is an attribute of the user, so it does NOT belong in a separate table but in the user table - even your choice of primary key for your state tables shows that) and one for the links - one user has many links. You don't need a separate autoincrementing id as suggested above, you just make the userID and link the joint primary key. ie links {userId, link} so just two columns. This now allows you to add as many links for each user as you wish, and also makes searching for people who have some links in common, as you now only have to search the two one column in the links table. Using multiple columns as in your first "design" would require you to query every single column that contained a link and compare it to every other column, for every other person - which would be totally the wrong way to solve such a problem. Normalising the data enables you to do many queries more easily, including queries you didn't think of. Imagine having to alter the table design every time you realise you need a few more link options for the user. The two table approach enables you to store one or one hundred or one hundred thousand links for one person.
It would be a good idea to start reading up on normalisation if you wish to make better databases.
PS If you decided to store more info about each state - capital, population, area, say, THEN you'd need a separate state table AND a user-state table as well
state---<user-state>---user
with user-state just containing the primary keys for user and state, as a joint primary key for that table.
State---User is a many to many, resolved as two one to many relationships.Dr John
www.kidneydialysis.org.uk
-
Jan 3, 2009, 19:27 #4
- Join Date
- Dec 2007
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So my schema would look like this?
Users
- user_id
- firstname
- lastname
- state
- pass
Links
- user_id
- link
Where a select on the Links table (with 3 users, each with 3 links each) would look something like:
Code:user_id link 1 http://www.yahoo.com 1 http://www.google.com 1 http://www.cnn.com 2 http://www.hotmail.com 2 http://www.gmail.com 2 http://www.abcnews.com 3 http://www.nytimes.com 3 http://www.amazon.com 3 http://www.ebay.com
I have read some normalization, but find entity relationship modeling easier to understand.
-
Jan 3, 2009, 20:34 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jan 4, 2009, 13:02 #6
- Join Date
- Dec 2007
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Just wondering if my schema from post #4 looks OK?
-
Jan 4, 2009, 13:12 #7
- Join Date
- Nov 2008
- Posts
- 172
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Looks good to me :-)
-
Jan 4, 2009, 13:48 #8
- Join Date
- Dec 2007
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
How about my SQL to create the tables? I've added a registration_date column to the users table (the spacing below is just for readability):
Code:CREATE TABLE users ( user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(40) NOT NULL, state CHAR(2) NOT NULL, email VARCHAR(60) NOT NULL, pass CHAR(32) NOT NULL, registration_date DATETIME NOT NULL, PRIMARY KEY(user_id), UNIQUE (email), INDEX login (email, pass), INDEX state (state) ); CREATE TABLE links ( user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, linkurl VARCHAR(60) NOT NULL, PRIMARY KEY(user_id) );
- state will be a dropdown with 2-letter state abbreviation values (CA, TX, WA), hence a CHAR(2)
- pass is declared CHAR(32) as I'm just going to use md5
- email is to be unique so the same email address can only be reigstered once
- email/pass is indexed for faster login lookups?
- state is indexed for faster search by state lookups?
A couple of notes on the links table:
- user_id declared exactly the same as it's counterpart in the users table
- linkurl declared as NOT NULL, though not entirely sure if this is needed? Obviously if a user adds a link record, they'll be providing some value.
-
Jan 4, 2009, 14:03 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
your tables are excellent
yes, linkurl should be NOT NULL
nice job
-
Jan 4, 2009, 14:32 #10
- Join Date
- Dec 2007
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
When I check the structure of the users table in phpMyAdmin, under indexes it gives me a warning/error message:
UNIQUE and INDEX keys should not both be set for column `email`
Is this something I should be concerned with or is it not really an issue?
-
Jan 4, 2009, 15:14 #11
- Join Date
- Nov 2008
- Posts
- 172
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
VARCHAR(60) for your linkurl is a bit iffy if you ask me. The url of the page we are on now (http://www.sitepoint.com/forums/show...23#post4099623) passes that up easily. How about 255?
-
Jan 4, 2009, 15:14 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jan 4, 2009, 15:53 #13
- Join Date
- Dec 2007
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Yeah, though what I'm doing isn't really realistic to begin with, it's more academic than anything else. URLs might not have been the best "data" choice, it's just what I thought of....I was just going to dump in root-level domains when I populate, http://www.yahoo.com, http://www.google.com, etc.
Excellent point, though, I'll update that column.
-
Jan 4, 2009, 16:07 #14
- Join Date
- Jul 2003
- Location
- Kent
- Posts
- 1,921
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Not quite right
This bit
Code:CREATE TABLE links ( user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, linkurl VARCHAR(60) NOT NULL, PRIMARY KEY(user_id) );
Code:CREATE TABLE links ( user_id SMALLINT UNSIGNED NOT NULL, linkurl VARCHAR(60) NOT NULL, PRIMARY KEY(user_id, linkurl) );
ie the schema would be
Links { user_id, linkurl }Dr John
www.kidneydialysis.org.uk
-
Jan 4, 2009, 16:20 #15
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
well spotted, dr john, i can't believe i missed that
-
Jan 4, 2009, 17:12 #16
- Join Date
- Dec 2007
- Posts
- 120
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jan 4, 2009, 17:18 #17
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jan 4, 2009, 20:13 #18
- Join Date
- Nov 2008
- Posts
- 172
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Bookmarks