table layout

mbaldwin577

Hi,
I am looking for some input on mysql table design for my project. I have been reading about db normallization and stuff, but I want some thoughts on a live project from experienced developers.

i am working on a financial script, mostly to gain more experience.
I understand that duplicate information should be kept in different tables, so that would be like if a user had multiple email addresses associated with their account, or if something could be used in multiple records. Like the state and country for each user for their address. Am I on the right path of thinking here?

I figure i am going to have 2 main tables, and most of the other tables are going to be sub-tables, and some of those might even have their own sub-tables. Not sure that is the right term for them.

For my users table I have
id, parent id, first name, last name, address line 1, address line 2, city, state, zip, country, phone, email address, password, signup date, signup ip, last login date, last login ip, email code (random code sent in signup email to confirm account), account confirmed, active, newsletter, tmp password (if they forget and the system generates a password and sends it to them, this would be set to 1), user level

the parent id is for the main user, I will eventually have it so users can creat additional users on their account, and assign them certain privledges. is this the recommended way of doing it, or should I do a different table mapping them, like

table of additional users would have
id, user id, and additional user id

I have thought of putting the last login information in a different table, so I could track the logins more, instead of the most recent one.

The user level would be the level id the user is assigned in the system, like free, basic, advanced, business etc.. and each of those levels would come with differen privleges as well. Like a free user might only be able to create 3 accounts, while an advanced user could create 15 different accounts and an additional user.
I think my user level table would look like

id, name, description, cost, period (monthly, quarterly, yearly etc),

then another table of user permissions that would have

id, name, count

this would hold things like number of accounts allowed 3, but then i would need something like that for each user level, so maybe more like free user acounts 3, basic user accounts 10, advanced user accounts 15, etc. Some values would not even have a count, like custom categories, the level of user either has the permission for that, or they didn't.

then a table that maps the user levels to the user permissions.

id, user level id, user permission id

Sorry i do not have this in MYSQL to provide and actuall dump, if that would be easier i could add all this in and put that code here.

Hope you could follow what I am doing to provide some thoughts, if not, just ask.

Any thoughts are welcome, and suggestions if I am not doing this the best, or recommended way. When i get the user part down, i will problaby ask about the other tables as well.
If there have been previous posts or articles on something like this, feel free to point me in that direction. i was not able to find much that covered what I am looking for.

Thanks,
Michael

bradgrafelman

mbaldwin577;11013437 wrote:
I understand that duplicate information should be kept in different tables

Er... not really; duplicate information shouldn't be kept anywhere - just keep a single copy of the data and simply reference it multiple times.

alexoldshane;11013439 wrote:
so that would be like if a user had multiple email addresses associated with their account, or if something could be used in multiple records. Like the state and country for each user for their address. Am I on the right path of thinking here?

In a roundabout way, I suppose, yes. What should really be said is if two piles of data are related in a one-to-many (e.g. one user, many e-mail addresses) or many-to-many (e.g. many user levels, many users) fashion, you'll want to keep the two piles of data in their own separate tables and create a "cross-reference" table to relate the two. Note that this extra table is really only necessary in the many-to-many relation; for the one-to-many relation, you can simply store the reference value (e.g. an ID number) for the "one" entity in each row of the "many" entities.

alexoldshane;11013439 wrote:
I figure i am going to have 2 main tables, and most of the other tables are going to be sub-tables, and some of those might even have their own sub-tables. Not sure that is the right term for them.

I don't think that's the right term you're looking for, either, since all tables are created equal. A table is a table is a table... at least, that's how it is in every SQL DBMS I've used (granted, that list is by no means extensive).

alexoldshane;11013439 wrote:
For my users table I have
id, parent id, first name, last name, address line 1, address line 2, city, state, zip, country, phone, email address, password, signup date, signup ip, last login date, last login ip, email code (random code sent in signup email to confirm account), account confirmed, active, newsletter, tmp password (if they forget and the system generates a password and sends it to them, this would be set to 1), user level

You might want to change columns like "state" and "country" to be references to state and country values you store in your DB, that way you keep the data consistent. In other words, User A doesn't say his state is "KS", User B says its "Kansas", and User C says its "KANSAS".

alexoldshane;11013439 wrote:
the parent id is for the main user, I will eventually have it so users can creat additional users on their account, and assign them certain privledges. is this the recommended way of doing it, or should I do a different table mapping them, like

table of additional users would have
id, user id, and additional user id

Again, this ultimately depends on which type of relationship you want to describe. If a given user account can only be linked to at most one other account (e.g. you don't have a single account being a "sub"-account of two or more other accounts), then what you've got is a one-to-one relationship and there's no need for the extra table. You'd simply let the 'parent id' column be a NULL (and probably DEFAULT NULL) column and leave it in the users tab

alexoldshane;11013439 wrote:
then another table of user permissions that would have

id, name, count

But the relationship between a given user level and its "count" value is one-to-one, so why would you create a separate table for this information? Why not just include the "count" column in the "user level" table?

Additionally, why do you have another "name" column in this extra table?

alexoldshane;11013439 wrote:
this would hold things like number of accounts allowed 3, but then i would need something like that for each user level, so maybe more like free user acounts 3, basic user accounts 10, advanced user accounts 15, etc.

... which means it would make more sense to simply add the "count" column to the "user level" table rather than this proposed additional table.

alexoldshane;11013439 wrote:
Some values would not even have a count

... which suggests that the "count" column should be a NULL column. Others might think that 0 would be an acceptable value to use instead, but to me that's conceptually wrong. To each his own, though.

alexoldshane;11013439 wrote:
then a table that maps the user levels to the user permissions.

id, user level id, user permission id

Now I'm confused... can a given user level be related to multiple "user permissions" ? If not, then again, this is a one-to-one relationship and this additional table makes no sense.

alexoldshane;11013439 wrote:
Sorry i do not have this in MYSQL to provide and actuall dump, if that would be easier i could add all this in and put that code here.

It would certainly help. We can talk about theoretical designs all day in a language as ambiguous as English (or any other human language, though I suspect English is one of the most ambiguous 😉), but most of us here are fully capable of "speaking code" instead and looking at a bunch of raw 'CREATE TABLE...' SQL statements to get an idea as to the full schema you're designing.

alexoldshane;11013439 wrote:
If there have been previous posts or articles on something like this, feel free to point me in that direction. i was not able to find much that covered what I am looking for.

What were you looking for? If you're designing your own product XYZ, searching for things like "how to design database for XYZ" won't be helpful at all. 😉 Instead, you'll probably have to be able to step back a bit and look at what you're doing at a higher level (which is what I was trying to do above, e.g. with the 1-1, 1-N, N-M relationship descriptions and all).

As for actual articles and whatnot, I'm afraid I won't be of any help there; what I haven't learned through years of experience (read: trial and error, Google'ing, asking for critiques, etc.) I learned from professors as I got my degree.

Weedpacket

bradgrafelman wrote:
mbaldwin577 wrote:
For my users table I have
id, parent id, first name, last name, address line 1, address line 2, city, state, zip, country,

You might want to change columns like "state" and "country" to be references to state and country values you store in your DB, that way you keep the data consistent. In other words, User A doesn't say his state is "KS", User B says its "Kansas", and User C says its "KANSAS".

And User D says it's "QLD".

What I'm getting at there is that - unless it serves an actual purpose to the business logic, there's little justification for splitting the address out into multiple fields. If you have got a reason to record the state (e.g., different shipping rates for WA, BC and HI), then split that out into a distinct field that refers to the canonical postal state table. But what purpose does "address line 1", "address line 2" serve? (And what about "address line 3"?)

bradgrafelman wrote:
though I suspect English is one of the most ambiguous

Heresy!

mbaldwin577

bradgrafelman
thanks for replying, and I'll try to make some clairifications.

As far as the additional users, although that is highly unlikely an additional user would have more than 1 parent user, I guess it is possible. Example, if i set up an account, and had my wife as an additional user, then my mother-in-law setup and account, it is likely she would add my wife as an additional user. Although i can see this having other issues, like the additional user selecting who their parent user is for the session. that could be determined by a simmple script after the login that sees they have multiple parent users and have them select the one they are working with for that session. So now that I have thought through it with your help, maybe the mapping table would be the best way to go. I guess that could also mean a parent user could be an additional user under a different account. Example I could have my own, then my mom signs up, and she adds me as an additional user. Adds more complexity to the script, but I love a challenge.

here is the proposed users table.

CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(35) NOT NULL,
`LastName` varchar(35) NOT NULL,
`Address1` varchar(75) NOT NULL,
`Address2` varchar(75) NULL,
`City` varchar(35) NOT NULL,
`State` varchar(25) NOT NULL,
`Zip` varchar(15) NOT NULL,
`Country` varchar(25) NOT NULL,
`EmailAddress` varchar(75) NOT NULL,
`Password` varchar(75) NOT NULL,
`SignupDate` datetime NOT NULL,
`SignupIP` varchar(15) NOT NULL,
`LoginDate` datetime NOT NULL,
`LoginIP` varchar(15) NOT NULL,
`EmailCode` varchar(25) NOT NULL,
`Confirmed` int(1) NOT NULL,
`Active` int(1) NOT NULL,
`Newsletter` int(1) NOT NULL,
`TmpPassword` int(1) NOT NULL,
`UserLevel` int(5) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `EmailAddress` (`EmailAddress`))
ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `user_mapping` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ParentUserID` int(11) NOT NULL,
  `SubUserID` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

I do actually have tables for the state and country, but I have the name put in the user tables, not the id, because I was not sure how to get the state and country without doing a few different select statements.
Is having the name put in okay, or should it be the ID?

CREATE TABLE IF NOT EXISTS `state` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CountryID` int(5) NOT NULL,
  `name` varchar(35) NOT NULL,
  `abbr` varchar(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;

CREATE TABLE IF NOT EXISTS `country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) NOT NULL,
  `abbr` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

I haven't ever used joins and stuff, but I am guessing that is what I would use if I put the state and country id's in the user table, maybe something like this?

SELECT users.*, state.name, country.name FROM users, state, country WHERE state.id = users.StateID AND country.id = users.CountryID AND users.id = $user_id

As far as the user levels go, let's see if I can explain it a little better.
I guess it would be kind of like the different levels of users in Word Press. You can have subscribers, authors, editors, admins etc. Each level has different permissions, and the permissions can be used for different levels of users, like the admin would have all the permissions of the other users. So in some cases it could be a 1 to 1, but in other cases, it could be a 1 to many relationship.
One proposed structure with a little data.

CREATE TABLE IF NOT EXISTS `user_levels` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `description` varchar(255) NOT NULL,
  `price` decimal(5,2) NOT NULL,
  `terms` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `user_levels` (`id`, `name`, `description`, `price`, `terms`) VALUES
(1, 'free', 'free membership level', '0.00', 'lifetime'),
(2, 'basic monthly', 'Basic level of membership', '4.97', 'monthly'),
(3, 'basic yearly', 'yearly subscription for the basic membership level', '48.00', 'yearly'),
(4, 'advanced', 'advanced level of membership with more options', '7.97', 'monthly');

CREATE TABLE IF NOT EXISTS `user_permissions` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `count` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `user_permissions` (`id`, `name`, `count`) VALUES
(1, 'CustomCategories', NULL),
(2, 'AdditionalUserManagement', NULL),
(3, 'AccountsAllowedFree', 3),
(4, 'AccountsAllowedBasic', 8);

CREATE TABLE IF NOT EXISTS `permission_mapping` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `LevelID` int(10) NOT NULL,
  `PermissionID` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

INSERT INTO `permission_mapping` (`id`, `LevelID`, `PermissionID`) VALUES
(1, 1, 3),
(2, 2, 2),
(3, 2, 4),
(4, 3, 2),
(5, 3, 4),
(6, 4, 1),
(7, 4, 2);

Maybe I should do a mapping table to map the user id to the permission id? The additional user created by the parent user might not have the same permissions as the parent user, but they can not be more then the parent users permissions. those permissions are to be determined by the parent user. So change the UserLevel in the users table to UserPlan, and that could be NULL by default for additional users, and change the title of the user_level table to user_plans. The user_permissions can stay the same, but the mapping table would have the UserID, and the PermissionsID mapped. So that would be a many to many relationship, cause there is multiple users, and multiple permissions correct?

I really do appreciate the help. Google has been a great friend to, but there is nothing like having stuff looked over by people with experience.

Weedpacket,
yeah, for what I am doing, I problaby do not need any of the address info, unles I want to send them a post card for signing up. Two address lines seem to be the norm, and most of the addresses i have seen on more than 2 lines can be put on 2 fine.

Thanks,
Michael