0

I am setting up a database that represents different types of content. I have a link (think of it as a URL) that can point at different types of data over time, say a phone number, an email address, or a web page.

I figured I would make tables like:

  • content_phone
  • content_email
  • content_url

I also then have a link table that needs to point at a particular type of content.

The content that a link can point to changes over time (time X it points at a URL time X + 1 it points at a phone number, etc...)

The content types have no fields in common, and I do not expect that they ever will (not sure if that makes a difference).

I am stuck for a nice way to hook the link and the content type together.

EDIT:

There are a large number of unrelated tables for content types. Some tables may have 10 fields.

TofuBeer
  • 60,850
  • 18
  • 118
  • 163

3 Answers3

1

Create one parent table (e.g. called "Content") that has the key from the other content tables. That way you can always reference that one table elsewhere instead of several different possibilities. This avoids needing nullable columns. Example:

http://consultingblogs.emc.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx

nvogel
  • 24,981
  • 1
  • 44
  • 82
1

Super-type/subtype is usually recommended when there are some common fields between entities, but in your example it may be useful anyway.

Here are few examples of similar questions-answers:

Community
  • 1
  • 1
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
0

It looks like you want to have a revision field in there

create table contact
   person_id
   phone ..
   email ..
   url ..
   updated_at
)

then you can pull from the table as such

SELECT * FROM person JOIN contact ON ( person.id = contact.person_id ) ORDER BY contact.updated_at DESC LIMIT 1

-daniel

Daniel
  • 7,006
  • 7
  • 43
  • 49
  • I don't really want to have all the null values though... also it isn't really contact information, there could be things like, say, license plate or gelolocation (not that that matters for the purpose of the discussion). – TofuBeer Dec 04 '10 at 01:20