Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: One table or two?

    Hi,

    I remember getting an answer to a question like this before but I couldn't find it in my historical posts.

    Suppose I've a member's table storing information such:

    Table members

    Member ID
    Username
    Password hash
    Email
    Points
    Date joined

    Does it make sense to move Points out of the member's table into a separate Points table?

    Table points

    Member ID
    Points

    "Points" stores the points earned in a game - every user has only one Points record.

    Should Points (or similar columns) all go into the table Members?

    Thanks for reading and I look forward to your replies

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2 View Post
    every user has only one Points record.
    single table, unless you have multiple games and want to track their points separately
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Many thanks, r937

    Now I've convinced I need to alter my faulty table designs of yesteryear.

  4. #4
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    What if only a handful of the entire membership are assigned particular value, for instance, to recognise them as special members?

    Do we use a separate table in this case - one row for each of these special members - or do we have a column in the Members Table for this value (e.g. 0 for the majority, some Enum value for this small group of members)?

    Last edited by pearl2; 08-04-11 at 12:22.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2 View Post
    Do we use a separate table in this case - one row for each of these special members - or do we have a column in the Members Table for this value (e.g. 0 for the majority, some Enum value for this small group of members)?
    i truly believe it's six of one or half dozen of the other

    but for goodness' sake, don't use an ENUM!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thank you so much

    So either will do - first time I came across that idiom, so had to google

    But why is ENUM bad? What is the alternative? CHAR(1)?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ENUM is the spawn of the devil

    (you could google that phrase as well)

    better would be a foreign key to a table of acceptable values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Ah I see.

    So it's a good practice to have those values in a separate table so that if changes are needed in future, there's no need to modify the ENUM values in the main table. Someone else other than the original coder could do it...

    I read in one of the results I found that contains your reply. Someone wrote that ENUM('1', '2', '3') is bad. The MySQL site says it's better to avoid numeric enums.

    But if the purpose is to define a small numeric range of say 1-5, wouldn't ENUM('1', '2', '3', '4', '5') make sense?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2 View Post
    But if the purpose is to define a small numeric range of say 1-5, wouldn't ENUM('1', '2', '3', '4', '5') make sense?
    no, it wouldn't

    if the purpose of the range of numbers is to define actual numbers (e.g. the number of legs a person has), then TINYINT is more appropriate

    but if the numbers represent something else, then why use numbers? assuming you were okay with using ENUM in the first place, why not use an ENUM which enumerates the actual values?

    in my opinion, if they aren't numbers, then a foreign key to a table containing the actual values is still better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2011
    Posts
    24
    Quote Originally Posted by r937 View Post
    in my opinion, if they aren't numbers, then a foreign key to a table containing the actual values is still better
    I think ENUM is a better choice if you are certain that the values is NOT going to change. For example YES/NO (MySQL 'user' table use this), ACTIVE/PENDING/INACTIVE etc..

    select on this colum will be faster too as it will avoid join (which you have to if using the foreign-key way).

    Please correct if i'm wrong
    Last edited by reeson; 08-04-11 at 23:36.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by reeson View Post
    ... as it will avoid join (which you have to if using the foreign-key way)
    you are probably under the mistaken impression that all foreign keys have to be integers, and that's not actually true

    there's nothing wrong with this --
    Code:
    CREATE TABLE statuses
    ( status VARCHAR(99) NOT NULL PRIMARY KEY
    );
    INSERT INTO statuses VALUES
    ('ACTIVE'),('PENDING'),('INACTIVE');
    the foreign key would then use the actual status value, and so no join is required

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    May 2011
    Posts
    24
    Quote Originally Posted by r937 View Post
    you are probably under the mistaken impression that all foreign keys have to be integers, and that's not actually true

    there's nothing wrong with this --
    Code:
    CREATE TABLE statuses
    ( status VARCHAR(99) NOT NULL PRIMARY KEY
    );
    INSERT INTO statuses VALUES
    ('ACTIVE'),('PENDING'),('INACTIVE');
    the foreign key would then use the actual status value, and so no join is required

    yeah i guess you're right about the join thing.

    but if the value's not going to change, ENUM way is still better in term of space consumption (again, if this is wrong, tell me).

    and of course, you have less a table in your database.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by reeson View Post
    ENUM way is still better in term of space consumption (again, if this is wrong, tell me).
    if space really was a big deal -- and it isn't, but let's go there since you brought it up -- you would replace the firstname column in a personnel database with an integer foreign key to a table of first names, since there are so many Johns and Marys to be found, you would replace street names in the addresses table, since there are really only a limited number of streets in any city and these are shared by many addresses, etc. etc.

    most people don't bother with such space saving techniques, and i am always surprised how vehemently they insist that you have to save a couple of bytes on a status column, which is a piddly amount of potential savings by comparison

    Quote Originally Posted by reeson View Post
    and of course, you have less a table in your database.
    if the number of tables was a big deal -- and it isn't, but let's go there since you brought it up -- you would denormalize right down to 1NF or maybe even 0NF

    but nobody does that, right?


    in my opinion, the arguments ~for~ ENUM are extremely hard to find, and the arguments ~against~ are numerous

    but, hey, it's your database, knock yourself out, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    May 2011
    Posts
    24
    Quote Originally Posted by r937 View Post
    you would replace the firstname column in a personnel database with an integer foreign key to a table of first names, since there are so many Johns and Marys to be found, you would replace street names in the addresses table, since there are really only a limited number of streets in any city and these are shared by many addresses, etc. etc.
    they are mutually exclusive, you can do that AND use enum. you'll save even more space.

    Quote Originally Posted by r937 View Post
    if the number of tables was a big deal -- and it isn't, but let's go there since you brought it up -- you would denormalize right down to 1NF or maybe even 0NF
    same as above


    I'm not an enum groupie or anything, hardly even use them. Just trying to understand things better

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the amount of space saved is tiny, especially in these days when disk storage is measured in terrabytes or hundreds of gigabytes.

    providing you know that there are never ever going to be changes the argument for using an ENUM may seem seductive, however its relatively rare in the real world. its surprising how often seemingly closed groups such as ACTIVE/PENDING/INACTIVE can be changed by the bright sparks users. pushing it to a table with a FK devolves the responsibiulity for changing such things onto your users and doesn't require ANY developer / programmer or DBA resource. similarly if your target application is a mulitlingual application the swchema / design remains the same, the data changes as required.

    their may well be a theoretical performance hit, or even marginal real world performance hit in using a join, but I've never seen a significant performance problem on using joins. unless you know better that is.

    for gender I'd use M or F with a validation rule / check constraint so it can be one of three values 'M', 'F' or NULL. no need for an ENUM
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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