IS-A relation when defining tables

ddloracle-11g-r2

I already asked this question on SO, prior to realizing that there may have been a more suitable place.

I am trying to implement an IS-A relationship between tables. Say I have a singer and a band which are artists

I want every artist to have an ID which is inherited by the band or singer.

In the code I wrote it appears that a band and a singer may have the same ID. How do I prevent that? And is there a better way to implement this? Reading material is most certainly welcome.

CREATE TABLE artists
( id number not null primary key
);

CREATE TABLE singers
( id number not null primary key,
  name char(50) not null,
  last_name char(50) not null,
  foreign key(id) references artists(id)
);

CREATE TABLE bands
( id number not null primary key,
  name char(50) not null unique,
  contact char(50) not null,
  participants number not null,
  check (participants > 0),
  foreign key(id) references artists(id)
 );

insert into artists(id) values(1);
insert into singers(id, name, last_name) values(1, 'an', 'Bish');
insert into bands(id, name, contact, participants) values(1,'un','an',2);

as you can see I was able to add a band and a singer with the same ID, under the same "parent."

Best Answer

The best way to enforce uniqueness in this scenario then is to not split the artist "types" into separate tables. Normalizing your schema will show that there are fields in common, so at the least your artists table would contain id, artist_type ("band" or "singer"), and name. Any fields that are not shared should be split into more generalized tables.

EDIT:

After thinking about schema designs I've seen for music catalogues, the following comes to mind.

Be consistent in nomenclature. Yes, an "artist" is a "band" or a "singer." But a "singer" is also a "band" in the sense that they are a performer onstage, billed under a specific name ("Tori Amos" and not "Amos, Tori"), and usually backed by a band of musicians. A "singer" is also an "artist," but so is every member of a "band."

In other words, don't get caught up in what type of performer it is, just call it an "artist" if that is the name you've decided on. "Artists" can have 1 - n "members," which are just "people" with the normal details you would want to store about people. So your schema would consist of:

CREATE TABLE artists
( id number(10) not null,
  artist_name varchar2(50) not null,
  contact varchar2(50) not null,
  artist_type varchar2(50) not null, /* ...not sure you really need it */
  CONSTRAINT artists_pk PRIMARY KEY (id)
);

CREATE TABLE people
( id number(10) not null,
  first_name varchar2(50) not null,
  last_name varchar2(50) not null,
  contact_number varchar2(50) not null
);

CREATE TABLE artist_members
( id number(10) not null,
  artist_id number(10) not null,
  member_id number(10) not null,
  CONSTRAINT members_pk PRIMARY KEY (id),
  CONSTRAINT artists_fk FOREIGN KEY (artist_id)
    REFERENCES artists(id),
  CONSTRAINT members_fk FOREIGN KEY (member_id)
    REFERENCES people(id)
);