0

I have the following entities: Books, Authors, and Stores.

Each of them can have a comments section. Should I store the comments in a separate table OR have a subtype/supertype design? Is it technically wrong if I use separate tables? Because either way, it may required the same amount of work OR the subtype design may require more work if a supertype hierarchy changes for any subtype.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
firebird
  • 3,461
  • 6
  • 34
  • 45

1 Answers1

0

Supertypes and subtypes attack the issue "These things are not exactly alike, but they're also not utterly different."

A supertype/subtype design requires that some attributes be stored in the supertype, and some be stored in the subtype. The attributes for each thing in the real world are split between two tables.

How do you decide how to split up the attributes? The attributes common to all subtypes move "up" into the supertype. So if you were starting with companies and individuals, they're not exactly alike, because

  • one is an individual, the other is (conceptually) a bunch of individuals,
  • one can have children, the other can't,
  • one can get married, the other can't

and so on.

They're not utterly different, because

  • both can have multiple addresses, phone numbers, email addresses, web sites, etc.,
  • both can enter into contracts with other companies,
  • both can enter into contracts with other individuals,
  • both are required to file tax returns

and so on.

The attributes common to both (legal name, at the very least) bubble "up" into the supertype.

In your case, though, it's not clear how books, authors, and stores fit into that analysis. It's clear that they're not exactly alike. But are they utterly different? I think so.

If you're talking about something like web site posts about books, persons, and stores, that's a different story. The answer to a similar SO question includes code.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • The comments apply to books, authors, and stores (the attributes for the comments are the same between the 3 entities). Is it technically wrong to have 3 separate comment tables for each of the entities even though the comment attributes are the same (a table for each comment type etc)? Would it be considered normalized because you're repeating the comment attributes for each comment type table? – firebird Oct 10 '11 at 18:44
  • @firebird: Technically wrong? Maybe. (But it has nothing to do with normalization.) Tables that have overlapping meanings violate the principle of orthogonal design. See http://www.dbdebunk.com/page/page/622331.htm – Mike Sherrill 'Cat Recall' Oct 21 '11 at 22:57