A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.
2 format (paragraph)
source | link

I do not like both of your solutions.

  • Solution 1: Storing different things at the same place is a bad idea. Storing foreign key to different tables in the same field is worse.
  • Solution 2: this is also no a good idea. You get a lot of NULL values. The best thing is to draw a picture. My prefered solution is that of @Joel Brown (with some minor changes) Instead of 'text' i woul call the supertype 'message' and it should not only hold the content but the all fields (and references) common to all message types.

My prefered solution is that of @Joel Brown (with some minor changes) Instead of 'text' i woul call the supertype 'message' and it should not only hold the content but the all fields (and references) common to all message types.

subtype.png

message(
  message_id PK
  content
  common attributes ...
)

Tweet(
  message_id PK, FK
  tweet_username
  tweet attributes
)

Sms(
  message_id PK, FK
  sender_number
  sms attributes ...
)

Email(
  message_id PK, FK
  email_sender
  email attributes ...
)

The implementation is straight forward: each entity is a table. the superentity has a primary key. this primary key is used for the subentities too. If you want model your system with an entity text that has relationships to entities sms, email and tweet as described in your post, you get the following EER diagram

relsationship.png

I don't like this model in your special case but there are other situation where such a model is appropriate.

There is a standard way to transform such 1:1-entities to tables. we have three relations sends, mails, tweets which have an optional entity (SMS,Email,Tweet) and a mandatory Entity (Text). In this case the foreign key is stored in the table of the optional entity. (You did it the wrong way) So you get the following tables

text(
  text_id PK
  content
  other attributes ...
)

Tweet(
  tweet_id PK
  text_id FK
  tweet_username
  tweet attributes
)

Sms(
  sms_id PK
  text_id FK
  sender_number
  sms attributes ...
)

Email(
  email_id PK
  text_id FK
  email_sender
  email attributes ...
)

Using Extended Entity Relationship Diagrams (and maybe any other modelling method) makes it almost impossible to create without cheating such solutions that you have proposed. Diagrams can be drawn with pencil and paper. The diagrams in this post are created with DIA. The Extended Entity Relationship Model and how to transfer it into tables can be found in T.J.Teorey, D.Yang,J.P.Fry: A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model

I do not like both of your solutions.

  • Solution 1: Storing different things at the same place is a bad idea. Storing foreign key to different tables in the same field is worse.
  • Solution 2: this is also no a good idea. You get a lot of NULL values. The best thing is to draw a picture. My prefered solution is that of @Joel Brown (with some minor changes) Instead of 'text' i woul call the supertype 'message' and it should not only hold the content but the all fields (and references) common to all message types.

subtype.png

message(
  message_id PK
  content
  common attributes ...
)

Tweet(
  message_id PK, FK
  tweet_username
  tweet attributes
)

Sms(
  message_id PK, FK
  sender_number
  sms attributes ...
)

Email(
  message_id PK, FK
  email_sender
  email attributes ...
)

The implementation is straight forward: each entity is a table. the superentity has a primary key. this primary key is used for the subentities too. If you want model your system with an entity text that has relationships to entities sms, email and tweet as described in your post, you get the following EER diagram

relsationship.png

I don't like this model in your special case but there are other situation where such a model is appropriate.

There is a standard way to transform such 1:1-entities to tables. we have three relations sends, mails, tweets which have an optional entity (SMS,Email,Tweet) and a mandatory Entity (Text). In this case the foreign key is stored in the table of the optional entity. (You did it the wrong way) So you get the following tables

text(
  text_id PK
  content
  other attributes ...
)

Tweet(
  tweet_id PK
  text_id FK
  tweet_username
  tweet attributes
)

Sms(
  sms_id PK
  text_id FK
  sender_number
  sms attributes ...
)

Email(
  email_id PK
  text_id FK
  email_sender
  email attributes ...
)

Using Extended Entity Relationship Diagrams (and maybe any other modelling method) makes it almost impossible to create without cheating such solutions that you have proposed. Diagrams can be drawn with pencil and paper. The diagrams in this post are created with DIA. The Extended Entity Relationship Model and how to transfer it into tables can be found in T.J.Teorey, D.Yang,J.P.Fry: A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model

I do not like both of your solutions.

  • Solution 1: Storing different things at the same place is a bad idea. Storing foreign key to different tables in the same field is worse.
  • Solution 2: this is also no a good idea. You get a lot of NULL values. The best thing is to draw a picture.

My prefered solution is that of @Joel Brown (with some minor changes) Instead of 'text' i woul call the supertype 'message' and it should not only hold the content but the all fields (and references) common to all message types.

subtype.png

message(
  message_id PK
  content
  common attributes ...
)

Tweet(
  message_id PK, FK
  tweet_username
  tweet attributes
)

Sms(
  message_id PK, FK
  sender_number
  sms attributes ...
)

Email(
  message_id PK, FK
  email_sender
  email attributes ...
)

The implementation is straight forward: each entity is a table. the superentity has a primary key. this primary key is used for the subentities too. If you want model your system with an entity text that has relationships to entities sms, email and tweet as described in your post, you get the following EER diagram

relsationship.png

I don't like this model in your special case but there are other situation where such a model is appropriate.

There is a standard way to transform such 1:1-entities to tables. we have three relations sends, mails, tweets which have an optional entity (SMS,Email,Tweet) and a mandatory Entity (Text). In this case the foreign key is stored in the table of the optional entity. (You did it the wrong way) So you get the following tables

text(
  text_id PK
  content
  other attributes ...
)

Tweet(
  tweet_id PK
  text_id FK
  tweet_username
  tweet attributes
)

Sms(
  sms_id PK
  text_id FK
  sender_number
  sms attributes ...
)

Email(
  email_id PK
  text_id FK
  email_sender
  email attributes ...
)

Using Extended Entity Relationship Diagrams (and maybe any other modelling method) makes it almost impossible to create without cheating such solutions that you have proposed. Diagrams can be drawn with pencil and paper. The diagrams in this post are created with DIA. The Extended Entity Relationship Model and how to transfer it into tables can be found in T.J.Teorey, D.Yang,J.P.Fry: A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model

1
source | link

I do not like both of your solutions.

  • Solution 1: Storing different things at the same place is a bad idea. Storing foreign key to different tables in the same field is worse.
  • Solution 2: this is also no a good idea. You get a lot of NULL values. The best thing is to draw a picture. My prefered solution is that of @Joel Brown (with some minor changes) Instead of 'text' i woul call the supertype 'message' and it should not only hold the content but the all fields (and references) common to all message types.

subtype.png

message(
  message_id PK
  content
  common attributes ...
)

Tweet(
  message_id PK, FK
  tweet_username
  tweet attributes
)

Sms(
  message_id PK, FK
  sender_number
  sms attributes ...
)

Email(
  message_id PK, FK
  email_sender
  email attributes ...
)

The implementation is straight forward: each entity is a table. the superentity has a primary key. this primary key is used for the subentities too. If you want model your system with an entity text that has relationships to entities sms, email and tweet as described in your post, you get the following EER diagram

relsationship.png

I don't like this model in your special case but there are other situation where such a model is appropriate.

There is a standard way to transform such 1:1-entities to tables. we have three relations sends, mails, tweets which have an optional entity (SMS,Email,Tweet) and a mandatory Entity (Text). In this case the foreign key is stored in the table of the optional entity. (You did it the wrong way) So you get the following tables

text(
  text_id PK
  content
  other attributes ...
)

Tweet(
  tweet_id PK
  text_id FK
  tweet_username
  tweet attributes
)

Sms(
  sms_id PK
  text_id FK
  sender_number
  sms attributes ...
)

Email(
  email_id PK
  text_id FK
  email_sender
  email attributes ...
)

Using Extended Entity Relationship Diagrams (and maybe any other modelling method) makes it almost impossible to create without cheating such solutions that you have proposed. Diagrams can be drawn with pencil and paper. The diagrams in this post are created with DIA. The Extended Entity Relationship Model and how to transfer it into tables can be found in T.J.Teorey, D.Yang,J.P.Fry: A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model