Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    26

    Please review my database design

    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

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    Why do you have 2 seperate tables for User/Album?

  3. #3
    Join Date
    Jan 2006
    Posts
    26
    Quote Originally Posted by norie
    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.

  4. #4
    Join Date
    Apr 2006
    Posts
    33
    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.
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  5. #5
    Join Date
    Jan 2006
    Posts
    26
    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.

  6. #6
    Join Date
    Apr 2006
    Posts
    33
    Quote Originally Posted by Eiolon
    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
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  7. #7
    Join Date
    Jan 2006
    Posts
    26
    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...

  8. #8
    Join Date
    Apr 2006
    Posts
    33
    I'm more familiar with .NET/SQL Server than php/mysql, so Ill let someone else answer this question. Id store a hash of the users 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 dont know enough about this function to comment any further...
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

Posting Permissions

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