Jump to content

Archived

This topic is now archived and is closed to further replies.

franck

Design of database for rock local bands

Recommended Posts

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 :)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
You'll need to use a lookup table to handle many-to-many relationships, e.g. between musicians and bands.

Share this post


Link to post
Share on other sites
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 ...

Share this post


Link to post
Share on other sites
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".

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.