Entity Relationship Diagram - Entity which can have multiple types

Question

How can I represent an Album entity which can be vinyl and/or cd and/or tape. I assume those can't be declared as subtypes of the Album entity as there would be no way the album could be all of them. Should it be a multivalue attribute of the Album entity? Also each type vinyl, cd, tape have different attributes (eg. vinyl might have colour, cd might have an associated video clip etc.)

And for an Artist entity how could I make a solo artist and a group artist? At the minute I have an Artist entity with subtypes of Solo (with more attributes) and Group (with a multivalue attribute of artists.)

I have put a relationship between Solo and Group (MemberOf) which is 1 Solo to Many Groups.

Is this correct or should I make it different?

Thanks

Have you done any research into the matter? like reading this for example? Your question sounds like you're asking for opinions and I don't see a problem stated anywhere. — Scott Solmer
IMO instead of making vinyl, cd or tape as subtype, i will make them category.. — Miller
Yes I have. The problem is how do I make an Album entity which can be any combination of vinyl,cd,tape with each type adding own attributes? Subtype would allow it to be one type but not all as far as I'm aware so I'm asking for a way to deal with this. The Artist question was an opinion based question. Thanks — Daniel Wardin
Subtype would allow it to be one type .... This is true for EXCLUSIVE subtype. Not correct in general, subtypes may be overlap. — Damir Sudarevic
Thanks a lot! Didn't know there were overlapping subtypes in ER! You're a star! — Daniel Wardin

Benny Hill Benny Hill · Accepted Answer · 2013-10-18T15:10:33

I would probably start with something like this and then adapt it based on other needs:

albums
    id              unsigned int(P)
    name            varchar(100)
    artist_id       unsigned int(F artists.id)
    released        date
    ...

+----+------------------+-----------+------------+-----+
| id | name             | artist_id | released   | ... |
+----+------------------+-----------+------------+-----+
|  1 | ...But Seriously |         1 | 1989-11-07 | ... |
|  2 | Invisible Touch  |         2 | 1986-06-09 | ... |
|  3 | Heaven and Hell  |         3 | 1980-04-25 | ... |
| .. | ................ | ......... | .......... | ... |
+----+------------------+-----------+------------+-----+

artists
    id              unsigned int(P)
    name            varchar(50)
    ...

+----+------------------+
| id | name             |
|  1 | Phil Collins     |
|  2 | Genesis          |
|  3 | Black Sabbath    |
|  4 | Ronnie James Dio |
|  5 | Dio              |
|  6 | Mike Rutherford  |
| .. | ................ |
+----+------------------+

artists_artists
    id              unsigned int(P)
    artist_id       unsigned int(F artists.id)
    group_id        unsigned int(F artists.id)

+----+-----------+----------+
| id | artist_id | group_id |
+----+-----------+----------+
|  1 |         1 |        2 |
|  2 |         4 |        3 |
|  3 |         4 |        5 |
|  4 |         6 |        2 |
| .. | ......... | ........ |
+----+-----------+----------+

cds
    id                  unsigned int(P)
    album_id            unsigned int(F albums.id)
    ...

+----+----------+-----+
| id | album_id | ... |
+----+----------+-----+
|  1 |        1 | ... |
|  2 |        2 | ... |
| .. | ........ | ... |
+----+----------+-----+

colours
    id                  unsigned int(P)
    name                varchar(20)

+----+------+
| id | name |
+----+------+
|  1 | Red  |
|  2 | Blue |
| .. | .... |
+----+------+

tapes
    id                  unsigned int(P)
    album_id            unsigned int(F albums.id)
    ...

+----+----------+-----+
| id | album_id | ... |
+----+----------+-----+
|  1 |        1 | ... |
|  2 |        2 | ... |
| .. | ........ | ... |
+----+----------+-----+

vinyls
    id              unsigned int(P)
    album_id        unsigned int(F albums.id)
    colour_id       unsigned int(F colours.id)
    ...

+----+----------+-----------+-----+
| id | album_id | colour_id | ... |
+----+----------+-----------+-----+
|  1 |        1 |         1 | ... |
|  2 |        1 |         2 | ... |
|  3 |        3 |         3 | ... |
| .. | ........ | ......... | ... |
+----+----------+-----------+-----+

Entity Relationship Diagram - Entity which can have multiple types

1 Answers