Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a 'Text' table, the text table may came from different source, like Tweet, SMS, Email, etc. and each source have their own log, and Text is came from the source. so i came with some solution, but i don't know which is the best.

Solution 1 :

Text(
text_id PK
content
source_type (tweet, sms, email, etc)
source_id (can't be FK since it may contain sms_id, tweet_id, or email_id)
)

Tweet(
tweet_id PK
tweet_text
tweet_username
etc...
)

Sms(
sms_id PK
sms_text
sender_number
etc...
)

Email(
email_id PK
email_text
email_sender
etc...
)

Solution 2 :

Text(
text_id PK
content
sms_id FK
tweet_id FK
email_id FK
)

Tweet(
tweet_id PK
tweet_text
tweet_username
etc...
)

Sms(
sms_id PK
sms_text
sender_number
etc...
)

Email(
email_id PK
email_text
email_sender
etc...
)

Please note that one 'Text' only came from one source.

so if I use solution 2, let say the source is came from sms then the data will look like this

Text(
12
'the text'
333
null
null
)

Sms(
333
'the text'
0818833733664
etc...
)

so which is the best approach ?

UPDATE : I miss a big part on my question, and that's are may fault, sorry for that. In my case the Text also may came from a csv(excell) file. which mean one file may have many text, because the file is a dump for many text(coma or row separated). SMS, Tweet, and Email relation with Text is one to one, but File with Text is One to Many so the big missing part in my question is the File Table

File(
file_id PK
file_name
etc..
)
share|improve this question

2 Answers 2

The best approach for this type of situation is to use logical sub-typing. Keep columns that are common in a central table which also contains a partitioning attribute. Then keep sub-type specific columns in sub-type tables which have identifying 1:1 relationships with the common table.

In your case, this would look something like this:

Text(
text_id PK
content
source_type (tweet, sms, email, etc)
)

Tweet(
text_id PK, FK
tweet_username
etc...
)

Sms(
text_id PK, FK
sender_number
etc...
)

Email(
text_id PK, FK
email_sender
etc...
)

EDIT:

Note that the partitioning attribute (Text.source_type) is not strictly necessary, since it's value can be derived by checking for the existence of the key in the sub-type tables. If you choose to persist the partitioning attribute in the super-type table then you need to write some procedural code to ensure that the partitioning attribute values are consistent with the sub-type table records.

Note too, that if you want to ensure that each text is of only one type, you need to create some procedural code that enforces this restriction too.

share|improve this answer
    
+1 for subtyping and its implementation. But I think that the 'partitioning attribute' is not necessary. I is an overhead and can bring inconsistencies. –  miracle173 Jan 4 '13 at 14:03
    
@miracle173 - Agreed, the partitioning attribute is derivable and requires extra code to ensure consistency if it is to be persisted at the super-type. Thanks for the +1. I'll update my answer to reflect your comment. –  Joel Brown Jan 4 '13 at 14:36
    
Hi, Thanks for your answer, i really appreciate. but i think, i miss a big part on my question, and that's are may fault, sorry for that. In my case the Text also may came from a csv(excell) file. which mean one file may have many text, because the file is a dump for many text(coma or row separated). SMS, Tweet, and Email relation with text is one to one, but File with Text is One to Many. Thanks before –  Ahmad Jan 4 '13 at 15:03
    
@Ahmad - The way I would handle there being one kind of subtype that is different from the others is to keep the columns (and also distinct related entities) in just the subtype. Therefore, if your CSV/Excel subtype needs a child table to track multiple entries, then just keep either an empty value in Text.content or some reasonable summary value, like the contents of the first cell, or a placeholder like "(See Details...)" or whatever suits your needs. –  Joel Brown Jan 4 '13 at 15:19

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

share|improve this answer
    
superb, thank you for your answer, btw i find this link(dba.stackexchange.com/questions/24903/…) that may be similar to my question. What do you think of the mvaraujo answer ? –  Ahmad Jan 4 '13 at 19:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.