I am going to be using MySQL but I am sure that doesn't matter right now as I should get the normalization down first.

This is my first attempt at a database design and after reading about normalization over and over again 'til my head hurts I still don't think I have it clear. If someone could be kind enough to review my database structure and comment it would be very helpful.


PROJECT GOAL: Have users search a database of music albums and add them to their collection page.

PK = Primary Key


TABLE: Users
FIELDS: UserID(PK), UserName, Password

TABLE: Albums
FIELDS: AlbumID(PK), AlbumTitle

TABLE: Collection
FIELDS: UserID(PK), AlbumID

TABLE: AlbumInfo
FIELDS: AlbumID(PK), Composer, Publisher, Year, Genre

TABLE: UserInfo
FIELDS: UserID(PK), FirstName, LastName, Location

Why do you have 2 seperate tables for User/Album?

Do you mean why did I separate Users and UserInfo tables as well as Albums and AlbumInfo?

If so, I did that solely because I saw various articles doing the exact same thing for their examples. The articles said unnecessary information should be separated. For example, if someone is finding the albums in someones collection, they don't necessarily need to know the album info. So separating them would speed up the process as the database grew.

But those are just from articles I have read which influenced the way I designed the database. If it is unnecessary, then that's the type of stuff I want to know. But I would also like to know why so I learn from it.

Combine tables that have a 1:1 relationship with each other - Users and UserInfo, Albums and AlbumInfo.
By combining these tables you don't produce any redundant data - If users don't need the extra information in the AlbumInfo table, simply don't include the columns in the select statement.
By storing Album and AlbumInfo in the same table you reduce the complexity of your database and the number of joins required to query the data (this will speed up your queries). Also, you won't pay any additional performance penalty for inserts.

There is little argument for seperating this data into seperate tables, except that the row size will be smaller, increasing the number of rows that can be cached in memory - If you very rarely query the data in the 'Info' tables and your tables are *massive*, you might see a performance improvement.

1:1 relationships should definitly be the exception rather than the rule.

Ok, I have revised it:

TABLE: Users
FIELDS: UserID(PK), UserName, Password, FirstName, LastName, Location

TABLE: Collection
FIELDS: UserID(PK), AlbumID

TABLE: Albums
FIELDS: AlbumID(PK), AlbumTitle, Composer, Publisher, Year, Genre

So when it comes to creating a relationship to these tables, do I join these fields?

UserID (from Users) and UserID (from Collection)
AlbumID (from collection) and AlbumID (from Albums)

Also, does that mean I must make AlbumID in Collection and FK? Thanks for your help.

The Collection table primary key is a composite of UserId and AlbumId. You should also create foreign key constraints to enforce the relationship. The DDL for the Collections table would be something like this:

create table Collection
(
UserId int not null references Users(UserId),
AlbumID int not null references Albums(AlbumID)
constraint pk_Collection primary key (UserID,AlbumID)
)

Your select statement would be something like:

select --Colum List--
from Users u
join Collection c on u.UserId = c.UserId
join Albums a on a.AlbumId = c.AlbumId

A quick note - If you plan to store passwords in a database you should look at hashing or encryption. If this is a college project you probably don't need to do this.

Hope this helps,

David

Thanks for your help. This is a personal project in an attempt to learn PHP/MySQL.

So with password encryption, I was going to use PASSWORD('password') statement - unless there is a better way...

I'm more familiar with .NET/SQL Server than php/mysql, so I’ll let someone else answer this question. I’d store a hash of the user’s password in the database and validate the user input against the hash in the server side code. You might also consider using SSL depending on the nature of the application. As this is a personal project to learn php/mysql, the password function is probably sufficient to secure users passwords. I don’t know enough about this function to comment any further...