Need to assign multiple attributes to records - should I opt for one table or multiple tables?

I am using an MVC pattern to represent a table with many records of music albums. To each music album there are attributes (genre, artist, ratings, etc). Each attribute can have multiple values (so the genre of one album can be both "pop" and "latin", for example). I want to represent those attribute values using table(s).

So there are two basic approaches that I can think of开发者_运维知识库. I wonder which approach is better?

1. Have a separate table for each attribute (e.g., GENRE, ARTIST). The columns in each table will be album_id and attr_value.

2. Have a single table ATTRIBUTES which will also include, in addition to the album_id and value, the attribute name ("genre", "artist", ...).

Typically I would opt for method 1 (relational DB and all that), but if I choose method 2, my thought is that when I want to add a new attribute, I don't need to create a new model, controller, and view.

Any opinions?

This isn't so much an MVC issue its a Normalization question.

There is a process for normalizing your database and establishing entities (tables). The two typical forms are 3rd Normal Form or the Boyce-Codd Normal form. Searching for either should provide ample information. Now that said there are a few other designs you can use other than standard normalization. It all depends on how you want to balance errors (update/insert) and performance. Many people have been advocating for non-relational designs (nosql, couchdb, and folks who believe that the old concerns about corruption due to empty columns are unneeded today). Then there's the reality that serialized arrays open up the possibility of hybrid designs. You seem, to be more debating EAV (entity attribute value) vs additional table. EAV has a reputation of being a slower design, but really useful when the input units won't be know ahead of time. So with EAV if I have an artist and I want to add a "column" hometown, I dont have to create a new table of column, simply a new entry in the attributes table. EAV is also notoriously hard to validate and type.

In my products, I play it safe and go relational (Boyce-Codd form). Yes it means more models and more relationships, but its worth a few extra hours. Besides in MVC frameworks like the tagged Cakephp, it couldn't be much easier to make models. Everytime I've used EAV I've wished I just put the extra time into planning it out more.

Option 3: For the obvious and most widely used attributes use dedicated tables, and have another generic table for user-defined attributes (eg. "Annoys Ex-wife How Much")

There ought to be a pretty static set of attributes for the static tables.

I would treat Album and Genre as two distinct models. You can then create tables for each attribute/model in addition to another table mapping the relationship between them. The database structure would look something like this:

=====
Table: Albums
-- id
-- name
-- artist

====
Table: Genres
-- id
-- name

====
Table: Album_Genres
-- id
-- album_id
-- genre_id

Just add all your albums and genres into the relevant table then to state that a album belongs to a specific genre create a new row in the Album_Genres table. If you need to add/remove any attributes in future it's as simple as creating/deleting a couple of tables.

Whenever able - I use one table to represent one 'real thing' and when that isnt enough, such as when you have to relate two different ideas/object/things, I use multiple tables.

That being said we both agree to use your #1 idea - but now you want to be able to add new attributes. This is usually handled easiest with a 'meta' table - a table that abstracts the actual relationship of an object and its attributes - giving you what you want - dynamic attributes.

It could look like this

=======================
Table Name: Albums
----------------AlbumID
----------------GenreID --> foriegn key --> Genre in Genre Table
----------------ArtistID --> foriegn key --> Artist in Artist Table
----------------Name
----------------Etc (attributes that you know you will need)
=======================
Table Name: AlbumMeta
----------------AlbumID
----------------Key
----------------Value
=======================

Now - your albums can have concrete attributes (ie: attributes that all albums have) and if new business rules come up, say for only indie albums - you have a link to your new partner site "indiealbums.com" - you could create an AlbumMeta entry for each album in the genre of indie...

You should do this using 3rd Normal Form

In CakePHP the relation ships you are looking for and required fields are..

<?php
    class Album etends AppModel
    {
        public $name = "Album";
        public $hasAndBelongsToMany = array( 'Artist', 'Genre' );
    }
?>
<?php
    class Artist extends AppModel
    {
        public $name = "Artist";
        public $hasAndBelongsToMany = array( 'Album' );
    }
?>
<?php
    class Genre extends AppModel
    {
        public $name = "Genre";
        public $hasAndBelongsToMany = array( 'Album' );
    }
?>

// fields for the tables with their table names
// only the required ones for the relationships rest is up to you
artists
    --id

albums
    --id

genres
    --id

albums_artists
    --id
    --album_id
    --artist_id

albums_genres
    --id
    --album_id
    --genre_id

This means a bit more work initially entering the information. You have to treat the entry of the Genres and Artists as separate actions in the database. If you decide to treat artists as entire "bands" or as separate individual performers you will be able to with this setup as an album can be associated with an arbitrary group of artists.