Modeling and Designing a database

[magenta placenta]

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
• email
• 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?

[Kyle W]

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:

linkid auto INCREMENT
username
linkname

or something along those lines. So that you can group by username.

[Dr John]

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.

[magenta placenta]

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.

So my schema would look like this?

Users

• user_id
• firstname
• lastname
• state
• email
• pass

Links

• user_id
• link

Where a select on the Links table (with 3 users, each with 3 links each) would look something like:

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 can see why State should be in the users table (as it's just one attribute).

I have read some normalization, but find entity relationship modeling easier to understand.

[r937]

I have read some normalization, but find entity relationship modeling easier to understand.

they are essentially the same thing

[magenta placenta]

Just wondering if my schema from post #4 looks OK?

[Kyle W]

Looks good to me :-)

[magenta placenta]

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):

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)                                                                        
);

A couple of notes on the users table:

• 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.

[r937]

your tables are excellent

yes, linkurl should be NOT NULL

nice job

[magenta placenta]

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?

[Kyle W]

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?

[r937]

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?

not an issue

[magenta placenta]

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?

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.

[Dr John]

Not quite right
This bit

CREATE TABLE links (
	user_id 	SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	linkurl 	VARCHAR(60) NOT NULL,

       PRIMARY KEY(user_id)                                                                        
);

should actually be

CREATE TABLE links (
	user_id 	SMALLINT UNSIGNED NOT NULL,
	linkurl 	VARCHAR(60) NOT NULL,

       PRIMARY  KEY(user_id, linkurl)                                                                        
);

where user_id is the id from the user table i.e. a foreign key. You would NOT auto-increment it, as it would then have no relationship to the user_id in the user table. You must set up a joint primary key using the two fields in the table, as shown above.
ie the schema would be

Links { user_id, linkurl }

[r937]

well spotted, dr john, i can't believe i missed that

[magenta placenta]

You would NOT auto-increment it, as it would then have no relationship to the user_id in the user table.

Ah yes, of course, that makes perfect sense. The old copy/paste didn't quite work out for me. It's important that all the other attributes are the same, though, yes? A foreign key's attributes should be the same as it's corresponding primary_key in another table?

[r937]

A foreign key's attributes should be the same as it's corresponding primary_key in another table?

yes, and if you want to be sure, then use InnoDB tables and actually declare them as FOREIGN KEYs

[Kyle W]

well spotted, dr john, i can't believe i missed that

Same