Rather than trying to put attributes and sub-attributes all in one table, put the information in separate tables. For example, you might decide to have the following tables for the basic entities:1. Bands2. Artists3. InstrumentsThen you would have link tables - for example:1. ArtistToBandLink2. InstrumentToArtistLink.You may even have additional tables - for example, Bookings, Recordings etc.Here is the part for Bands and Artists with some sample data. I am only showing a sample, but you probably would have many more columns in the Bands and Artists table - but make sure that the columns belong to the entity. For example, you may have Artists address, phone numbers, etc. in the Artists table.CREATE TABLE Bands( BandId INT PRIMARY KEY, BandName VARCHAR(255));CREATE TABLE Artists( ArtistId INT PRIMARY KEY, Lastname VARCHAR(255), Firstname VARCHAR(255))CREATE TABLE ArtistToBandLink( BandId INT NOT NULL REFERENCES Bands(BandId), ArtistId INT NOT NULL REFERENCES Artists(ArtistId));---INSERT INTO Bands VALUES (1,'Band1'),(2,'Band2');INSERT INTO Artists VALUES (1,'Doe', 'John'), (2,'Doe','Jane'), (3,'Smith','Tom'),(4,'Jones', 'Mary');-- John Doe plays in Band1-- Jane Doe and Tom Smith play in Band2-- Mary Jones plays for both bands.INSERT INTO ArtistToBandLink VALUES(1,1),(1,4),(2,2),(2,3),(2,4);
multiple attributes
Posted - 2012-03-11 : 07:52:47
