MySQL table structure help

Tekime

Hi all,

I am trying to plan out a structure for my user data in the CMS I'm rebuilding. Also groups data. I have broken user info down into user profile and user options. I don't have a lot of experience designing DB's so I could use any tips on making this more efficient. Here are the structures I made up, please make suggestions, I would be very appreciative:

CREATE TABLE users
(
 userid int(6) unsigned not null auto_increment,
 username varchar(32) not null,
 password varchar(64) not null,
 PRIMARY KEY(userid)
)

CREATE TABLE user_profiles
(
 userid int(6) unsigned not null,
 firstname varchar(32) not null,
 lastname varchar(32) not null,
 email varchar(64) not null,
 location varchar(64) not null,
 homepage varchar(256),
 im_msn varchar(64),
 im_aol varchar(64),
 im_yahoo varchar(64),
 im_icq varchar(64),
 birthdate TIMESTAMP not null,
 interests varchar(256),
 occupation varchar(256),
 title varchar(64),
 created TIMESTAMP not null,
 lastlogon TIMESTAMP not null,
 PRIMARY KEY(userid)
);

CREATE TABLE user_options
(
 userid int(6) unsigned not null,
 remember enum('Y', 'N') NOT NULL default 'Y',
 invisible enum('Y', 'N') NOT NULL default 'Y',
 start_week enum('Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat', 'Sun') NOT NULL default 'Sun',
 time_offset tinyint(12) NOT NULL default '0',
 allow_email_notify enum('Y', 'N') NOT NULL default 'Y',
 PRIMARY KEY(userid)
)

CREATE TABLE groups_users
(
    userid int(8) unsigned not null,
    groupid tinyint(2) unsigned not null,
    PRIMARY KEY(userid)
}

CREATE TABLE groups
(
 groupid tinyint(2) unsigned not null auto_increment,
 name varchar(32) not null,
 create enum('Y','N') NOT NULL default 'N',
 publish  enum('Y','N') NOT NULL default 'N',
 view_all enum('Y','N') NOT NULL default 'N',
 edit_all enum('Y','N') NOT NULL default 'N',
 admin enum('Y','N') NOT NULL default 'N',
 PRIMARY KEY(groupid)
);

bastien

but
[red]birthdate TIMESTAMP not null[/red] should be a DATE attribute not timestamp..

Tekime

Thanks for your reply bastien. I actually changed birthdate to TIMESTAMP, since I wanted to keep all date values in my DB formatted as UNIX TIMESTAMP values. Think this is a bad idea for birthdate? I'll of course have to convert both ways...

Also do you think it's a good idea keeping user_profiles and user_options separate? They both will be accessed frequently and I wanted to break up the table a bit. Do you think it will make a performance difference?

Also if anyone can tell me, should I try to index any of these columns? I have no experience indexing but I want my DB to be designed as efficiently as possible, without having to redo it in a few months because something could be better. 🙁 If I understand correctly, I should index columns accessed frequently?

herve

Hi,

is there a reason to separate data of tables

users
user_profile

If, in your system, there's only 1 profile for 1 user, then
the data should be together in a single table.

Same question for user_options, if there's can only be one set
of options per user, you should stick everything in a single table.

Hervé.

Tekime

Well, the reason I split them is because I plan on adding quite a few more values to the 'options'. There will not be multiple profiles or options for users, but wouldn't it get slower with a huge table?

And for users_groups, is that sufficient? I think I should use this table to search for members of a particular group.

Anyway, is something like this better:

CREATE TABLE users
(
 userid int(6) unsigned not null auto_increment,
 username varchar(32) not null,
 password varchar(64) not null,
 firstname varchar(32) not null,
 lastname varchar(32) not null,
 email varchar(64) not null,
 location varchar(64) not null,
 homepage varchar(256),
 im_msn varchar(64),
 im_aol varchar(64),
 im_yahoo varchar(64),
 im_icq varchar(64),
 birthdate TIMESTAMP not null,
 interests varchar(256),
 occupation varchar(256),
 title varchar(64),
 created TIMESTAMP not null,
 lastlogon TIMESTAMP not null,
 remember enum('Y', 'N') NOT NULL default 'Y',
 invisible enum('Y', 'N') NOT NULL default 'Y',
 start_week enum('Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat', 'Sun') NOT NULL default 'Sun',
 time_offset tinyint(12) NOT NULL default '0',
 allow_email_notify enum('Y', 'N') NOT NULL default 'Y',
 PRIMARY KEY(userid)
);

Thank you, and if you could recommend another way to speed up or better optimize these tables please let me know 🙂

barand

Tektime

Is it true that a user can be in only one group?

if yes, put the group id in the user table and ditch the group_user table and put a non-unique index on groupid.

if no, remove the primary key (userid) from the group_user table and put normal non-unique indexeson group and user ids.

Tekime

barand, I believe I would like to allow users to be a member of multiple groups. So, I would go for the latter. Could you give me an example of using indexes on the user and group id's? Sorry, I'm a bit of a newb at SQL structuring; never used indexing. :o

barand

CREATE TABLE groups_users
   ( userid int unsigned not null,
    groupid tinyint unsigned not null,
    INDEX GU_UID (userid),
    INDEX GU_GID (groupid)
   )

sets up your table and creates 2 indexes, one called GU_UID and the other called GU_GID

Your table can now hold entries like

UID | GID |
1 | 1 |
1 | 2 |
2 | 2 |
3 | 2 |

where user 1 is a member of groups 1 and 2
group 2 has users 1,2,3

To select the group name and the users in a particular group, you need a query like

SELECT u.firstname, u.lastname, g.name FROM (users u INNER JOIN group_users gu ON u.userid = gu.userid) INNER JOIN
group g ON gu.groupid = g.groupid WHERE g.groupid = $gid

The indexes on group_user aren't essential, but it increases efficiency, and where supported, allows you to specify that a value in one index, say group_user.userid must exist in the user table (check out referential integrity and foreign keys)

hth

Tekime

I couldn't have asked for a more helpful explanation, Barand. Thank you very kindly 🙂 I've been reading up on indexing in the meantime, and you really helped tie all the things I read together into an actual implementation in MySQL.

I'll have a chance to play with this tonight. I think indexing the groups_users table is a good idea. If there were 10,000 users, the groups_users table could easily grow to 20,000 entries if a lot of users are in multiple groups.

I'm going to bug you with one other question. Bastien mentioned that birthdate should be a DATE type instead of a TIMESTAMP. This was actually how I had it in my old DB, but now I want to store all date information in UNIX TIMESTAMP format. Using the TIMESTAMP type, I tested an entry of my own birthdate in epoch seconds. It kept registering as "00000000000000". I used FROM_UNIXTIME to make the insert and it appeared to work.

So, there are actually two questions. Do you see any potential issues using TIMESTAMP for all of my date formats? And if not, is FROM_UNIXTIME the proper way to insert into this type?

Thanks again for the great explanation.

barand

First , a brief extract from the manual regarding the differences between date, datetime and timestamp fields

The DATETIME type is used when you need values that contain both date and time information.
MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.
The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. (\Supported"
means that although earlier values might work, there is no guarantee that they
will.)
The DATE type is used when you need only a date value, without a time part. MySQL
retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-
01-01' to '9999-12-31'.
The TIMESTAMP column type provides a type that you can use to automatically mark INSERT
or UPDATE operations with the current date and time. If you have multiple TIMESTAMP
columns, only the first one is updated automatically.

In the case of birtdates, unless you are in a maternity ward logging births as they occur, I'd agree with Bastien and go for DATE.

For appointments etc, go for DATETIME and for recording dataentry events, TIMESTAMP.

I'm attaching the full manual section on date time fields.

hth

Tekime

Hehe.. RTFM, right? Well, I do recognize that TIMESTAMP isn't normally intended for storing birthdays, I only thought it would be beneficial to store all of my dates in a common format. I did notice in the manual entry that TIMESTAMP(8) will store _only the day and year information (however, it still takes up the same amount of space, so it kind of defeats the purpose).

And no, lol, I am not logging births 🙂 I guess DATE would be a better choice, since I can keep my entry size down to 8 digits, and it is gasp intended for storing dates.

Thanks again for your great help, and patience with the less knowledgeable such as myself 😉 I'm going to double check all of my time/date types again and finish this structure.