SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot
    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
    • 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?

  2. #2
    SitePoint Zealot
    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.

  3. #3
    SitePoint Wizard
    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.

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr John View Post
    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:

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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by magenta placenta View Post
    I have read some normalization, but find entity relationship modeling easier to understand.
    they are essentially the same thing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just wondering if my schema from post #4 looks OK?

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2008
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks good to me :-)

  8. #8
    SitePoint Zealot
    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)                                                                        
    );
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your tables are excellent

    yes, linkurl should be NOT NULL

    nice job

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    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?

  11. #11
    SitePoint Zealot
    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?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by magenta placenta View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Dec 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kyle W View Post
    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.

  14. #14
    SitePoint Wizard
    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)                                                                        
    );
    should actually be

    Code:
    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 }

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    well spotted, dr john, i can't believe i missed that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Zealot
    Join Date
    Dec 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr John View Post
    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?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by magenta placenta View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Zealot
    Join Date
    Nov 2008
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    well spotted, dr john, i can't believe i missed that
    Same


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •