Design of database for rock local bands

[franck]

Hello everyone I've just joined this forum ... I dont know how i didn't do it before it is really good ... lots of programmers around, good job!!!.

Anyway I'm designing a website for local rock bands and i was wondering if i can get some ideas to improve the db I'm creating, like the correct types I'm using or the distribution of the data.

Check out the primary keys ... should i add more??
this is the dump i generated up until now (SORRY FOR MY CRAPPY ENGLISH)

[color=red]CREATE TABLE `album` (
  `idalbum` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `year` date NOT NULL,
  `idband` bigint(20) NOT NULL,
  PRIMARY KEY  (`idalbum`)
)[/color]

[color=green]CREATE TABLE `band` (
  `idband` bigint(20) NOT NULL,
  `bandname` varchar(255) NOT NULL,
  `year` date NOT NULL,
  `city` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `lyrictheme` varchar(255) NOT NULL,
  `style` varchar(255) NOT NULL,
  `biography` longtext,
  `interview` longtext,
  `imglogo` varchar(255) NOT NULL,
  `imgband` varchar(255) NOT NULL,
  `members` varchar(255) NOT NULL,
  `oldmembers` varchar(255) default NULL,
  `iduser` bigint(20) NOT NULL,  --this is to show who posted the band in the web
PRIMARY KEY  (`idband`)
)[/color]

[color=orange]CREATE TABLE `song` (
  `idsong` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `time` varchar(20) default NULL,
  `idalbum` bigint(20) NOT NULL,
  PRIMARY KEY  (`idsong`)
)[/color]

[color=purple]CREATE TABLE `user` (
  `iduser` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `birthdate` date NOT NULL,
  `imguser` varchar(255) NOT NULL,
  `favbands` longtext NOT NULL,
  `favstyle` longtext NOT NULL,
  PRIMARY KEY  (`iduser`)
)[/color]

All opinions are accepted ... and you all are invited to my site once it's finished :)

[fenway]

Well, you probably mean to have your PK field auto-increment and UNSIGNED; otherwise, I would simply suggest that you pull out the members field into another table, otherwise, you'll be storing non-atmoic data, which is bad.

[lilsim89]

Hey,

It looks like you want(or should) have any one or two, etc. albums to
correspond to a band. That's called a table relationship.
Use foreign keys to have that happen.

So, That means if you'd want to pull up a band, then pull up all albums they made, the foreign key would be the glue between it. It makes it A MILLION times easier to do so.

I would like to explain further, but knowing what DB you're using would make it easier.


I hope that helped you.

[franck]

In the table "album" I use this field that is a foreign key from the table band:
[color=green]`idband` bigint(20) NOT NULL,[/color]

Ain't that a good way to relate those 2 tables?? I did that in the rest of the tables too.

I'm using:

[color=red]-- phpMyAdmin SQL Dump
-- version 2.8.1
--
-- Servidor: localhost
-- Tiempo de generación: 09-09-2006 a las 21:15:16
-- Versión del servidor: 5.0.22
-- Versión de PHP: 5.1.4
--
-- Base de datos: `rockbd`[/color]

The id's of every single table are auto-increment ... when i tested with some data i could prove that. 8)

------------------------------------------------------------------------------------------
One question:
In the table "band" I use 2 fields called "members" and "oldmembers" what i want here is to show names of people who are or were members of the band and also would like to show a link to their respective bandss  ... I'm thinking about having a complete separate table called musicians (maybe) and have a foreign key field that stores the id of the band they are in

... but that's not a good idea ... some members could be in 2 bands or more at the same time, right??
Uhmm  ??? how could I solve that?

Thanks for your comments I'm trying to get some free time out of the university, so I can finished the design of the site ... i have a horrible schedule  :P

[franck]

By the way!!!! I was playing around with Corel Draw ... and this is what first came up as a design of the portal of music ... what do you guys think? ---- I think i like it.
All for the music, yeah!!  :D

[img]http://www.servidorhispano.com/web/metal.jpg[/img]

[fenway]

You'll need to use a lookup table to handle many-to-many relationships, e.g. between musicians and bands.

[franck]

You're very right ... the solution is to use a many-to-many ralationship between the tables "musicians" and "bands" ...

But in order to do that I would need to create an "intersection table" between those 2 and that will make me have a more consistence DataBase ... right???

I will call this intersection table "band-musician" ... then the result would have to be:

[color=blue]CREATE TABLE `band_musician` (
  `idband` bigint(20) NOT NULL,
  `idmusician` bigint(255) NOT NULL,
  PRIMARY KEY  (`iduser`, `idmusician`)
)[/color]

[color=red]CREATE TABLE `band` (
  `idband` bigint(20) NOT NULL,
  `bandname` varchar(255) NOT NULL,
  `year` date NOT NULL,
  `city` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `lyrictheme` varchar(255) NOT NULL,
  `style` varchar(255) NOT NULL,
  `biography` longtext,
  `interview` longtext,
  `imglogo` varchar(255) NOT NULL,
  `imgband` varchar(255) NOT NULL,[/color]
--  `oldmembers` varchar(255) NULL,      What do I do with this?
[color=red]
  `iduser` bigint(20) NOT NULL,  --this is to show who posted the band in the web
PRIMARY KEY  (`idband`)
)[/color]


[color=purple]CREATE TABLE `musician` (
  `idmusician` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `city` varchar(255),
  `country` varchar(255),
  `imguser` varchar(255),
  PRIMARY KEY  (`idmusician`)
)[/color]

The rest of the tables remain the same ...

[fenway]

Yeah, that looks good, though personally, I like to have a UID in every table.  As far as marking the members "old" or not, simply add a flag to the "intersection table".

[franck]

thanks for all your help, now ... I have to leave this project for the moment because of the period of exams that comes now for me at the university ... if anyone wants the sql dump to help me out while I'm "out" or just wants to practice ... send me a message and i will happily send it to you.

Thanks again ... wish me luck with my exams ;) bye