0
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?

KTastrophy
  • 1,739
  • 15
  • 23
Pascal
  • 12,265
  • 25
  • 103
  • 195

1 Answers1

0

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

KTastrophy
  • 1,739
  • 15
  • 23
  • @KatastrophY JFYI: The Folder holds templates of sketches. The Artist makes concrete images of these sketch templates. – Pascal Aug 01 '12 at 19:56
  • " So if you don't like null values you could do something like this." Well I like nulls. My question was rather can this design be MORE normalized ? – Pascal Aug 01 '12 at 19:58
  • If Artists make concrete images that use sketches. Then why don't you have an Images table and have that reference the artist who made it and the sketch its made? – KTastrophy Aug 01 '12 at 20:01
  • Technically, db designs can almost always be more normalized but working with the result can be painful if things are taken too far. – KTastrophy Aug 01 '12 at 20:03
  • ah damn I wrote images well please do not take that literally... my fault. About your aRtistSketch table: Ok its another table I just thought I can reuse the existing Sketch table for both Artists and Folders... Would you say its bad design if my Sketch table has this ArtistId which will be Null if its a Sketch for a Folder? Don`t want to get paranoid ;-) – Pascal Aug 01 '12 at 20:23
  • No its not bad design as long as that column being null/not null doesn't dictate what that table represents. Having Foo and Bar objects share a Foobar table where your code says its a Foo if columnA is null is far from normalized. – KTastrophy Aug 01 '12 at 20:36
  • The FolderID says its a Sketch related to a Folder. The ArtistId says its a Sketch related to a Artist. BUT if I insert a folder the artistId (FK) can be null (optional). Hope its more clear now. So I have the combination of column ArtistId is null AND FolderId has a value then its a Sketch for a Folder. Is that better design? Hm... – Pascal Aug 01 '12 at 21:01
  • I think your design is fine based on what you're telling me – KTastrophy Aug 01 '12 at 21:06
  • Well its stupid case for table design but that life... and I really do not want to introduce another table to keep it simple ;-) – Pascal Aug 01 '12 at 21:14