Jump to content

Design of database for rock local bands


franck

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 :)
Link to comment
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.
Link to comment
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
Link to comment
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]
Link to comment
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 ...
Link to comment
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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.