CODEWITHSUNDEEP
mysqldatabasenormalization
helpermethod
helpermethod

Reputation: 62165

Database Tables - normalized enough?

Here are two tables I designed for managing user accounts. 

create table if not exists users (
  id int unsigned not null auto_increment,
  username varchar(100) not null,
  password binary(60) not null,
  first_name varchar(100) not null,
  last_name varchar(100) not null,
  role_id int unsigned not null,
  primary key(id),
  unique(username)
);

create table if not exists roles (
  id int unsigned not null auto_increment,
  role varchar(100) not null,
  primary key(id),
  unique(role)
);

I think I need to normalize the first table, e.g. splitting the first table into some sort of user_info(first_name, last_name, ...) and account (username, password, role_id). The problem I have is that I am very uncertain of why I need to do this, as I can't really explain why it isn't in 3NF.

EDIT

A user can only have exactly one role (admin, poweruser, user).

Upvotes: 2

Views: 1148

Answers (3)

nvogel
nvogel

Reputation: 25526

Decompose the users table further only if it's allowable to have a user id and username without a corresponding first name and last name. Otherwise it looks like your tables are already in 5NF.

Upvotes: 2

Allan
Allan

Reputation: 17429

You only need to separate the user information and account information if a user can have multiple accounts or an account can have multiple users. If the user-to-account relationship is always 1-to-1, then you're normalized as is.

Occasionally it makes sense to separate out columns in a 1-to-1 relationship if the columns in the second table will be used rarely. However, in this case, it seems as though both tables would always be populated, so there's nothing to be gained by separating those columns.

Upvotes: 2

Thomas Berger
Thomas Berger

Reputation: 1870

I'm not a SQL Expert, but this tables looks very normalized to me. You should normalize a table to save space:

If you have a column, like role and you have 20 users with 5 roles, each roles uses 10byte, you will have 20 * 10bytes = 200bytes.

But if you normalize the table, as you have done it already, you will only need 5 * 10bytes = 50bytes for the role name, 5 * 1byte = 5byte for the id in the role table and 20 * 1byte = 20byte for the id in the user table.

  • 200bytes not normalized
  • 50bytes + 20bytes + 5bytes = 75bytes in normalized form.

This is only a very incomplete and basic calculation to show the background.

Upvotes: -1

Related Questions