Should i make the Painting and Sculpture table weak entities? Does that mean they don't need primary keys

user_16007327 0
Viewed 41

So I have three tables: Art, Sculpture and Painting (The latter two are subclasses of the first one). Since the subclasses already have the ArtID reference, in my opinion, they don't need their own primary keys (SculptureID and PaintingID). Should I make these subclasses weak entities (meaning I'll remove the primary keys). And in they ERD diagram, should I represent them as weak entities?

What I really want to ask is, can a superclass have subclasses that are weak entities? If there is a foreign key reference in the subclasses, would they still remain a weak entity?

    --Creating Subclass of Asset; Art itself is a superclass
CREATE TABLE Art(
    ArtID   Varchar(50) NOT NULL PRIMARY KEY,
    ArtName Varchar(50),
    AssetID VARCHAR(50) FOREIGN KEY REFERENCES Asset(AssetID),
    ArtistId VARCHAR(50) FOREIGN KEY REFERENCES Artist(ArtistID),
    MadeBy VARCHAR(50) FOREIGN KEY REFERENCES Artist(ArtistName),
    CONSTRAINT UC_Person UNIQUE (ArtName)
);

--Creating table for Art types: Painting :
CREATE TABLE Painting(
    PaintingID  Varchar(50) NOT NULL PRIMARY KEY,
    ArtID VARCHAR(50) FOREIGN KEY REFERENCES Art(ArtID),
    CanvasType VARCHAR(50),
    
);
--Creating table for Art types: Sculpture :
CREATE TABLE Sculpture(
    SculptureID Varchar(50) NOT NULL PRIMARY KEY,
    ArtID VARCHAR(50) FOREIGN KEY REFERENCES Art(ArtID),
    Material VARCHAR(50),
    Style VARCHAR(50),
    Dimension VARCHAR(50),

);
sql-server sql database
1 Answers

When you say "remove the primary keys" I think what you really mean is "remove the surrogate keys. The subclass tables would both still have a primary key, but the primary key would be ArtID. This would be both a primary key as well as a foreign key to the Art table, creating a "zero or one to one" relationship from each subtype to the parent type.

The short answer is that yes, you should remove the surrogate keys. They are not serving any purpose unless you explicitly want to be able to have one artwork be referenced by more than one painting or more than one sculpture, which seems semantically unlikely. In fact the only thing they are doing right now is allowing that many-to-one relationship to happen, which I expect would be a mistake in the model.

Having said that, I can imagine a scenario where you might want to say "this artwork is made up of multiple sculptures and I want to track the sculptures independently", but given that you are referring to this as a subtyping relationship, that doesn't seem to be the situation you're in.

The next question is: is this an exclusive subtype relationship? That is to say, is it true that an artwork can be either a painting, or a sculpture, but not both? Or is it true that an artwork can be both a sculpture and a painting?

Again, using normal language semantics, I expect the former is true - the artwork must be either a painting or a sculpture, not both. So it's an exclusive subtype relationship.

If that's true, then the following schema does not guarantee the desired subtype exclusivity, because nothing prevents me from putting the same ArtId in both the Paintings table and the Sculptures table (I am going to use plural table names, because tables are sets):

create table Artworks (ArtId int primary key);
create table Paintings (ArtId int primary key, foreign key references Artworks);
create table Sculptures (ArtId int primary key, foreign key references Artworks);

So the next question is, how strictly do you want the database to enforce the real world domain constraints? The above schema "works" if you just want a place to store data. It doesn't model all of the domain constraints, i.e. it allows data to exist that would contradict the real world being modelled. But enforcing the domain constraints correctly means doing more work on the schema, and adding things that look "strange", like this:

create table Artworks 
(
   ArtId int unique, 
   SubType char check (SubType in ('p', 's')),
   primary key (ArtId, SubType)
);

create table Paintings
(
   ArtId int,
   SubType char default ('p') check (SubType = 'p'),
   primary key (ArtId, SubType),
   foreign key (ArtId, SubType) references Artworks
);

create table Sculptures
(
   ArtId int, 
   SubType char default ('s') check (SubType = 's'),
   primary key (ArtId, SubType),
   foreign key (ArtId, SubType) references ArtWorks
);

This now enforces the exclusive subtype relationship. But it's "a bit strange", because now the Artworks table has a check constraint on it that is aware of all possible subtypes. This is an odd sort of dependency inversion - the subtype obviously has to be an artwork, but it's not the case that artworks should have to be aware of all possible subtypes. But in this relational implementation, enforcing that exclusivity does require it. It also means if you want to add more subtypes later you have to update the constraint on Artworks. So we've enforced the domain constraint at the cost of creating a bit of necessary smelliness in the schema.

There is a lot more that could be said on this topic. As The Impaler mentioned in their comment, there are a few ways of doing subtyping in a relational model, and at the end of the day there are tradeoffs in terms of schema complexity, domain integrity, and the amount of typing required to query the system.

But no matter what you decide to go with, I say remove the surrogate keys. They are only doing harm.

user_71652790
Related