How do you resolve many-to-many collection object in DBMS?
I am trying to model artists and songs and I have a problem where I have Song_Performance can be performed by many artists (duo for example), so I have Artist_Group to represent who the songs are being performed with.
So, I now have many, many relationships between Artist and Artist_Group, where Artist_Group is uniquely identified by the collection of artists in that group. I can create an intersection object representing artist participation in Artist_Group (Artist_Group_Participation?)
I am having trouble how to create a primary key for an Artist_Group object that preserves the fact that the same set of artists represents the same group and does not have a primary key for the Artist_Group object, m there is no foreign key for the Artist_Group_Participation object.
In the book Data Modeling by John Karlis and Joseph Maguire, this form is mentioned and referred to as a "multi-manifold organization" and states that it is very rare, but does not indicate how to resolve it. many-to-many cannot be stored directly in the DBMS. How can I imagine this?
Edit:
It looks like everyone is suggesting an intersection table, but that's not my problem. I have it. My problem is that you cannot add an Artist_Group entry where the group of artists it contains is the same as an existing group, ignoring the order. I thought the id for Artist_Group is varchar, which is a concatenation of the various artists that make it up, which would solve the problem if order was relevant, but having an Artist_Group for "Elton John and Billy Joel" does not prevent the group from being added for "Billy Joel and Elton John".
a source to share
I think I am missing the point of the Artist_Group relationship.
The data model in my mind:
Artist: human.
Song: The song itself.
Performance: A specific performance or song arrangement. Usually it will have one song, but you can provide an m: n link table to accommodate the mix. Ideally, this would be one real performance, i.e. There would be a related date.
Write: a specific fixed version of the performance (CD or whatever). Typically there is only one entry in Performance, but with a separate table the Grateful Dead / multiple-bootleg script will be handled as well as album re-releases, live radio and video playback versus CD versions, etc.
Performance_Artists: a table of linking from a specific performance to a list of artists. Each of them can also have an attribute describing their role (s) in performance (vocalist, drummer, etc.).
There is no explicit relationship between the set of performers, other than the fact that they share common characteristics. Thus, any table that tries to match random sets of performers outside of the recording context is not an accurate relational model, since there is no real relationship.
If you are trying to represent an explicit relationship between a set of performers (i.e. they are in the same group), well, groups have names that are unique (although this is not enough to be a primary key), and the group can be stored simply as Artist and then have an Artist_Member link table that links to individual artist records. Or you can have a separate Band table and Band_Members table to assign artists to it, possibly with membership dates. Anyway, just remember that band members change over time and band roles change from one song to the next, so bringing the band together with a performance should not replace linking performances directly to the participating artists.
a source to share
The primary keys for Artist and Artist_Group will be a numeric, incremental identifier. Then you will have a table Artist_Group_Participation which has two columns: artist_id and group_id. These will be foreign keys that refer to the ID of their respective tables. Then to select everything you would use JOIN.
EDIT: Sorry, I misunderstood your question. The only other way I can think of is to add a column "artists" to the Artist_Group table containing a serialized array (assuming you are using PHP, but other languages have equivalents) of artists and their IDs. Then just add a UNIQUE constraint to the column.
a source to share
You can make each artist ID match a bit in the bitfield. So if Elton John is ID 12 and Billy Joel is ID 123, then the "band" formed by the duo between Elton John and Billy Joel is Artist_Group
ID 10633823966279326983230456482242760704 (that is, it has 12th and 123rd bits).
You can force the link using the intersection table. For example, using a constraint CHECK
in PostgreSQL:
CREATE TABLE Artist_Group_Participation (
artist_id int not null,
artist_group_id int not null,
PRIMARY KEY (artist_id, artist_group_id),
FOREIGN KEY (artist_id) REFERENCES Artists (artist_id),
FOREIGN KEY (artist_group_id) REFERENCES Artist_Group (artist_group_id),
CHECK (B'1'<<artist_id & artist_group_id <> 0)
);
Admittedly, this is a hack. This adds added value to the surrogate key Artist_Group
when surrogate keys must be unique but not contain information.
Also, if you have thousands of artists and new artists every day, things can get cumbersome because the length of the data type has Artist_Group
to keep increasing.
a source to share
I don't have much experience with RDBMS. However, I have read Codd articles and CJ Date books.
So, instead of using RDBMS jargons, I'll try to explain more general sensory terms (at least for me!)
Here -
-
Singers' names should be standard based on "First Name - Last Name"
-
Each "Singer" must have an entry in the "Artists" table, even if they have performed solo
-
Each entry in the "Artists" group will consist of several "Singers", sorted alphabetically. There must be a single meeting of a certain combination.
-
Each song will have a unique recording from the "Group of Performers" regardless of whether they are solo, duet or in a band.
I don't know if that makes sense, but that's my two cents!
a source to share