-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
After the good comments I've got on this mailing list, I think I have the
structure more or less complete. In some cases I follow the comments, in some
others, I've improvised (hehehe).
So, this is the (explained) structure, what do you think ? Anything I can
improve ?
Everything starts with persons, actual human beings (this is a very simple
table):
CREATE TABLE `persons` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`birthdate` date default NULL,
`deathdate` date default NULL,
`bio`,
PRIMARY KEY (`id`)
)
Then we have the groups of (one or more) people:
CREATE TABLE `groups` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
)
Since the groups or made of people, there's a table which says who belonged to
what group and for what period of time:
CREATE TABLE `memberships` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`person` bigint(20) unsigned NOT NULL default '0',
`group` bigint(20) unsigned NOT NULL default '0',
`from` date default NULL,
`to` date default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `artistGroupFromTo` (`person`,`group`,`from`,`to`)
)
As some extra information, it can be specified what did this persons do in
that group (for that period of time) in the following table, since what a
person can do in a group is likely to change and grow (specially grow, I can
even say that in the future we'll invent more instruments so more roles will
be added as people perform those instruments in a group):
CREATE TABLE `membershipRoles` (
`membership` bigint(20) unsigned NOT NULL default '0',
`role` bigint(20) unsigned NOT NULL default '0',
UNIQUE KEY `membership` (`membership`,`role`)
)
Now, another point of entry to the system. The songs... this table defines the
abstract concept of song (language is a three letter code of the language of
the song, to be matched agains another table[1]):
CREATE TABLE `songs` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`language` char(3) NOT NULL default 'eng',
`lyrics` longtext,
PRIMARY KEY (`id`)
)
I was tempted to add a field 'translationOf' to easily hold translations of
songs. What do you think about that ?
Now, a song can be performed, so, I have the following table for performances
(either live or studio):
CREATE TABLE `performances` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`song` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
)
And then who (which group) did what (which role) in a song, the role is the
same as for `membershipRoles`:
CREATE TABLE `performancesGroups` (
`performance` bigint(20) unsigned NOT NULL default '0',
`group` bigint(20) unsigned NOT NULL default '0',
`role` bigint(20) unsigned NOT NULL default '0'
UNIQUE KEY `performanceGroupRole` (`performance`,`group`,`role`)
)
The third end of this whole thing, are albums:
CREATE TABLE `albums` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`cover` longblob,
`coverFormat` varchar(255) default NULL,
PRIMARY KEY (`id`)
)
Now, the albums can have a lot of related information, like, who made them,
that is the main group, like Queen, The Beatles, Eric Clapton:
CREATE TABLE `albumsGroups` (
`album` bigint(20) unsigned NOT NULL default '0',
`group` bigint(20) unsigned NOT NULL default '0',
UNIQUE KEY `album` (`album`,`group`)
)
The, to specify who did what in that album (for example, Freedie Mercury:
Vocals, John Lenon: Guitars, Whoever Knowshim: Producer, etc):
CREATE TABLE `albumsPersons` (
`album` bigint(20) unsigned NOT NULL default '0',
`person` bigint(20) unsigned NOT NULL default '0',
`role` bigint(20) unsigned NOT NULL default '0',
UNIQUE KEY `album` (`album`,`person`,`role`)
)
Now, each album contains a set of performances (not songs), in a specific
order:
CREATE TABLE `albumsTracks` (
`album` bigint(20) unsigned NOT NULL default '0',
`performance` bigint(20) unsigned NOT NULL default '0',
`track` tinyint(3) unsigned NOT NULL default '0',
UNIQUE KEY `albumPerformanceTrack` (`album`,`performance`,`track`)
)
And at last, the table of roles (the scope fields specifies what can be done
for each kind of data, it's more of a helper than anything else, a helper for
the GUI):
CREATE TABLE `roles` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`scope` set('album','performance','song') NOT NULL default
'album,performance',
PRIMARY KEY (`id`)
)
which for example, can contain the following data:
(1, 'Music composer', 'song');
(2, 'Lyrics composer', 'song');
(3, 'Vocals', 'album,performance');
(4, 'Guitars', 'album,performance');
(5, 'Bass Guitar', 'album,performance');
(6, 'Percussion', 'album,performance');
(7, 'Piano', 'album,performance');
So, in general, what do you think ?
Thank you!
- --
Pupeno: [EMAIL PROTECTED] - http://pupeno.com
Reading Science Fiction ? http://sfreaders.com.ar
[1] I'm making that table public in my web page here:
http://pupeno.com/misc/languagesDB
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
iD8DBQFB+AJxfW48a9PWGkURAvC8AJ9YeNxHCt+ZgfJrl4nvcbYxCJy+lwCfX4Rk
HxtIQOtUBlI2lQZmMMakoPw=
=IZH6
-----END PGP SIGNATURE-----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
