Database Architecture Theory

[nodirtyrockstar]

Right now I'm working on a website for a record label. I need to write a script that switches between bands and pops band info based on user selection. If it was just PHP, the fields would be: string (band photo; url src to an img), an array of strings (streaming music; multiple url src to mp3 links), string (duration; this one is actually two dates that represent a span of time), array of strings (players; key & value pairs that represent band members and what they do in the band, i.e. drummer, bassist, etc.), string (band bio; it will need to be a couple paragraphs). Since I need to store this information in a database, I am first planning the structure for the table(s) that I will need to create for this. For now, I have three areas of concern.

 

1. Regarding the arrays, if the absolute max number of key/value pairs is ten, is it okay to store those in a single field? Or should I create another table and make a compound key to reference the main band table?

 

2. I plan to use either blob or text field for band bio. Any tips you would like to offer about those two data types would be helpful, though I will be doing plenty of reading about them regardless.

 

3. As for duration, can anyone think of any reason I should actually break that into two date fields? I don't think there will ever be a reason to run any calculations on how long they have been together, so I am thinking a string will be fine. If you disagree with that assessment, I wouldn't object to hearing your reasoning.

 

Any input you can offer would be greatly appreciated!

Edited December 19, 2012 by nodirtyrockstar

[Barand]

1. arrays - normalise you tables properly and put as separate rows in another table

 

2. Use TEXT or VARCHAR for text. BLOB for binary data.

 

3. Use DATE type and keep your options open from the start - greater functionality than storing dates in a text field.

[nodirtyrockstar]

Thanks for answering, Barand. These answers seem on par with my expectations. I have some follow up questions.

 

1. I hear what you are saying. I need a little more help, though. Let's use the band member table as an example. I have one band with a relationship to many members, and there are many bands with this same relationship that all need to be stored. Do I have to make a table for each new band? That doesn't seem wise. So what is the unique key that I use in the band table to reference all the members of a particular band in the member table?

 

On a previous project I created a composite key, so I'm thinking that is the solution. However, I am struggling to figure out what other field I should combine in order to connect the band in the bands table to the members in the member table, since the band will be repeated in the members table. Does that make sense? (FYI I am using MyISAM, because my host does not offer InnoDB.)

 

2. I read online that fields containing large amounts of text can slow down queries. Would you agree with that? If so, do you recommend putting that into a separate table?

 

3. Noted. I will create two separate date fields.

 

4. I realized that I failed to mention that (of course) I will want to reference the records that each band has recorded, since the primary goal of this website is to sell records. Aside from each record entry containing a reference to the band, would you recommend any additional references between the band and the records?

Edited December 19, 2012 by nodirtyrockstar

[ignace]

One artist is part of one-or-more bands.

One band has one-or-more artists.

 

+-------------------------+
| artist                  |
+-------------------------+
| PK,AI | artist_id | INT |
+-------------------------+

+-------------------------+
| band                    |
+-------------------------+
| PK,AI | band_id | INT   |
+-------------------------+

+--------------------------+
| band_has_artist          |
+--------------------------+
| PK,FK | band_id   | INT  |
| PK,FK | artist_id | INT  |
| NN    | joined_at | DATE |
|       | left_at   | DATE |
+--------------------------+

 

One album has one-or-more songs.

One song is on one album (technically).

 

+--------------------------+
| song                     |
+--------------------------+
| PK,AI | song_id  | INT   |
| FK,NN | album_id | INT   |
| NN    | length   | INT   |
+--------------------------+

+--------------------------+
| album                    |
+--------------------------+
| PK,AI | album_id | INT   |
| FK,NN | band_id  | INT   |
+--------------------------+

Edited December 19, 2012 by ignace

[nodirtyrockstar]

Unfortunately, I do not have a degree in database administration, so I'm going to need some help understanding that last post.

[Barand]

1. One table will suffice. You need a unique key so just create an auto-inc id and add the bandID as a foreign key. I have attached a basic diagram for you. If you want to keep a track on members of the band moving on to another band then Ignace's schema allows for that. All depends on your requirements.

 

2. It should only be slower when you select that field - a reason for never using "SELECT * " in queries. If you need the bio then it makes little difference if it's in the same table or a separate one, so don't over complicate.

 

4. Again a separate table for the mp3 tracks available. Ignace's schema allows for albums then tracks within those albums if that's the way you want to go.

[nodirtyrockstar]

I'm going to think about this, work on trying to understand Ignace's post, and I'll post again probably tomorrow. Thanks for the input!