-----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]