Can this entity relationship be more normalized

Question

N Artists have M Folders.
1 Folder has N Sketches.

Artist

ArtistId (PK)

ArtistFolder

ArtistId (PK)(FK)
FolderId (PK)(FK)

Folder

FolderId (PK)

Sketch

SketchId (PK)
FolderId (FK)

What I want now is this:

1 Artist has N Sketches.

I would have to add a new field to table Sketch: ArtistId

This field would always be empty/null when I would insert a Sketch for a Folder.

Is there a better design you know?

Answer 1

I don't understand why Sketches would be given both an artistId and a folderId but I obviously don't know what you are creating.

It seems to me that you have multiple types of sketches. Some sketches are "created" by Artists and others by some other entity. Either way, they are given a folder. So if you don't like null values you could do something like this.

Sketch

SketchId (PK)
FolderId (FK)

Then:

ArtistSketch

SketchId (FK)
ArtistId (FK)

You could then have a constraint on ArtistSketch that disallows duplicate sketches.

To get a sketch for a given artist you could do a join across the two tables to get:

SketchId
ArtistId
FolderId

Otherwise, just query the Sketch table.

This would be an example of Class Table Inheritance: http://martinfowler.com/eaaCatalog/classTableInheritance.html

Doctrine, an ORM library for PHP has an interesting take on this for use with MySQL to reduce the unfortunate amount heavy lifting (code wise) required to make this work properly under many setups. http://docs.doctrine-project.org/projects/doctrine-orm/en/2.0.x/reference/inheritance-mapping.html