SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 42
  1. #1
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best database structure for this example

    Hi!

    I need to create a database of users who will be identified by the following fields: id (primary key), username, password, role.

    The role field would be a number that I'm going to use to show information only to certain users (if a user has role 3 he can see information entered by users of level 1, 2 and 3 but not info entered by users of role 4).

    Do you think it's better to keep a single table or is it better to split it into 2 tables, the first one containing the fields id, username and password and the second one containing id and role?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,037
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Every user needs to have a role right? If so, I don't see why you would split up the table and I would use just one table.
    Any reason you thought splitting it into two tables would be better?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Every user needs to have a role right? If so, I don't see why you would split up the table and I would use just one table.
    Any reason you thought splitting it into two tables would be better?
    Yes, just because in the book by Kevin Yank "Build your own database driven website using PHP in MySQL" there is an example in which he splits the db this way, and I though that it was better to ask why somebody would want to do such a thing

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,037
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    I don't actually have the book, but if he's using two tables then a user in his example can probably have more than one role at the same time. In your system this doesn't make sense. If a user has roles 2 and 4 for example, 2 is redundant because that's already implied by role 4.
    Hence, a user can only have one role at the time and you could put the field for the role in the users table.

    Does that make sense?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    357
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My guess why to use a 2nd table is to accommodate a user having multiple assigned roles simultaneously.

    Edit:

    I see i got beaten to the punch
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  6. #6
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    I don't actually have the book, but if he's using two tables then a user in his example can probably have more than one role at the same time. In your system this doesn't make sense. If a user has roles 2 and 4 for example, 2 is redundant because that's already implied by role 4.
    Hence, a user can only have one role at the time and you could put the field for the role in the users table.

    Does that make sense?
    Yes, definitely.

    Let's think about this example now: if, instead of a number, the privilege field would have been a String (example: "manager") then I probably would have had to create a second table to connect the string to the corresponding privilege level, right?

    Quote Originally Posted by Doug G View Post
    My guess why to use a 2nd table is to accommodate a user having multiple assigned roles simultaneously.

    Edit:

    I see i got beaten to the punch
    Yes, but in my example a user can have more than one role without having to create a second table

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,037
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by D3V4 View Post
    Let's think about this example now: if, instead of a number, the privilege field would have been a String (example: "manager") then I probably would have had to create a second table to connect the string to the corresponding privilege level, right?
    Right. If you implement the roles as privilege levels you would indeed a second table that ties the roles and their corresponding privilege level together.

    Quote Originally Posted by D3V4 View Post
    Yes, but in my example a user can have more than one role without having to create a second table
    Right again
    If the roles are all incremental that's a nice system.
    If roles are not linearly ordered like in your example then you would of course need another system.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  8. #8
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Right. If you implement the roles as privilege levels you would indeed a second table that ties the roles and their corresponding privilege level together.
    Would this work even if we kept the information in the same table? A role that is a string and a privilege level that is an integer?

    No wait, the system would be redundant this way...

  9. #9
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your case 1 table is all you really need.

    The only situations I can think of when you will need 2 or more tables are

    1 - where a user can have more than 1 role

    2 - where you have a role validation table in which fldRoleId is the primary key in the role validation table and fldRoleId in the user table is a foreign key to the validation table.

  10. #10
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Normalising the database will tell you exactly what tables you need and what each should contain. The only time you would construct your database differently from what the normalisation rules dictate based on your fields and the relationships between the fields is where you specifically decide to undo a normalisation for efficiency reasons with respect to sections of the data that are relatively static but which are frequently read.

    How that would apply to the particular fields referred to by the OP depends on whether a given user can have more than one role at the same time. If so then a second table is needed so that there can be a record in that table for each role. If not then only the one table is needed.

    Also with the data specified presumably only one user can have a given username and therefore that field ought to be the primary key and the id field can be dropped (unless usernames that are hundreds of characters long are allowed).
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  11. #11
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    How that would apply to the particular fields referred to by the OP depends on whether a given user can have more than one role at the same time. If so then a second table is needed so that there can be a record in that table for each role. If not then only the one table is needed.
    What if, as we were thinking, I would use an integer to specify the role of a person? This would work in a "pyramidal" hierarchy right?

    Also with the data specified presumably only one user can have a given username and therefore that field ought to be the primary key and the id field can be dropped (unless usernames that are hundreds of characters long are allowed).
    You are right, I put an id field because I wanted to avoid the problem of two users having the same name, but what are the odds of that ^^

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by D3V4 View Post
    ... I wanted to avoid the problem of two users having the same name, but what are the odds of that ^^
    zero point zero zero zero, assuming you don't want it to happen and prevent it with a database constraint
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    felgall explained it with some big words, but super-generalizing-and-simplifying, you want to prevent having redundant data in your database.

    You seem to already have a grasp on that when you pointed out the redundancy there would be in having the role and privilege level in the same database.

    Also, any time you are considering having a comma-separated list (such as, creating a comma-separated list of user-roles), that's a likely indicator that you need another table (comma-separated lists almost always equal bad).

    And if you want to prevent two users having the same name, you just make the field be "unique" (which is what r937 hinted at. )

  14. #14
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samanime View Post
    felgall explained it with some big words, but super-generalizing-and-simplifying, you want to prevent having redundant data in your database.

    You seem to already have a grasp on that when you pointed out the redundancy there would be in having the role and privilege level in the same database.

    Also, any time you are considering having a comma-separated list (such as, creating a comma-separated list of user-roles), that's a likely indicator that you need another table (comma-separated lists almost always equal bad).
    Thank you for your suggestions

    And if you want to prevent two users having the same name, you just make the field be "unique" (which is what r937 hinted at. )
    I wonder how I couldn't think about it

  15. #15
    SitePoint Wizard
    Join Date
    Apr 2007
    Posts
    1,398
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    No need to reinvent the wheels unless you want to for fun. Not sure if this fits your requirement but I would start from existing model and change accordingly.

    http://static.springsource.org/sprin...ix-schema.html

  16. #16
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by samanime View Post
    And if you want to prevent two users having the same name, you just make the field be "unique" (which is what r937 hinted at. )
    And the easiest way to contruct the table if you have one unique field is to make that the primary key (unless the length of the field is such tat it would be totally impractical to have that as the primary key). In the case of user names you will generally have a maximum length that they can be which will be small enough that having that as the primary key will be practical and adding an extra meaningless number field to perform the same task of distinguishing between users that the username already does will not be necessary.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  17. #17
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Melbourne, VIC, AU
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by D3V4 View Post
    You are right, I put an id field because I wanted to avoid the problem of two users having the same name, but what are the odds of that ^^
    I'd be more inclined to have a numerical ID in the event changing a username is an option (now or in the future). It removes the PK dependency on a changeable item then.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by W2ttsy View Post
    It removes the PK dependency on a changeable item then.
    why is this automatically considered a benefit?

    username is a great example of a column where you do not want changes to happen frequently, consequently the ON UPDATE CASCADE option is most appropriate here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by W2ttsy View Post
    I'd be more inclined to have a numerical ID in the event changing a username is an option (now or in the future). It removes the PK dependency on a changeable item then.
    and adds several dozen other complications in its place. The rare change of a userid is far easier to handle than all of the problems that adding an unnecessary id can cause. Plus adding the extra field means that the data is no longer properly normalised since tnot all the fields in the table have the same relationship to the key and nothing but the key any more.

    The only time you should be adding extra numeric keys to tables is when they have no natural key, when the natural key is too long to be used, or the key value changes too frequently (say twice or more since you started reading this).
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by felgall View Post
    Plus adding the extra field means that the data is no longer properly normalised
    in a word, no

    <snip/>
    Last edited by Mittineague; Oct 20, 2010 at 11:13. Reason: off-topic removed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    <snip/>

    There are fields there that are not dependent on the key, the whole key and nothing but the key and so it is not normalised.

    To get a table into third normal form you must "Remove non-key columns representing facts about other non-key columns into a separate table" so since all of the fields except the key and the username are dependent on the username to properly normalise that data you should split it into two tables with username as the primary key of the second table since all of the fields apart from username represent facts about that user.

    <snip/>
    Last edited by Mittineague; Oct 20, 2010 at 11:14. Reason: off-topic removed
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  22. #22
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sometimes I need to do a similar thing, but I usually create them in seperate table..

    users table
    id int primary key auto increment
    name
    email
    password
    role_id

    roles table
    id int primary key auto increment
    description

    That way, if I'm having any kind of user interface, I can have a drop down that shows the description of each role, rather than asking a use to select from 1 - 4. It also means, that should I need to rename a role at a later date, it's much easier to do so without effecting any of the logic of the site.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by littlened View Post
    ... should I need to rename a role at a later date, it's much easier to do so without effecting any of the logic of the site.
    how can you "rename" a role when it doesn't have a name?

    or were you thinking that you'd somehow need to give it a new auto_increment number? if so, why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    how can you "rename" a role when it doesn't have a name?

    or were you thinking that you'd somehow need to give it a new auto_increment number? if so, why?
    I think littlened was referring to if you give your roles names, such as Author, Manager, Administrator etc. In that case, you would need a second table to contain that data in the occurrence of a name change later. This makes more sense then using 1, 2, 3 as they have meaning.

    In the ID vs. username battle, I'm not going to enter that argument! Its all to do with personal preference and what you want your system to do. The normalisation rules do have some leeway for interpretation.

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Timme View Post
    In the ID vs. username battle, I'm not going to enter that argument! Its all to do with personal preference and what you want your system to do.
    totally agree

    Quote Originally Posted by Timme View Post
    The normalisation rules do have some leeway for interpretation.
    totally disagree
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •