Postgresql – Database Design – One table from many source(One to One)

database-designpostgresql

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..
)

Best Answer

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.

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

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

Related Solutions

Combined or separated tables: File and Message

I would definitely go with two separate tables.

customer wants to communicate by writing a message without having attachment (FilePath) or customer uploads any files without having any message (Content) written.

But with the way you're modeling it right now, there really isn't any relationship between file and message, as they both are dependent on Customer_ID. If you tried to query all files and messages for each customer, you wouldn't be able to tell when the two are related (except maybe by looking at the date).

And it sounds like a customer can have a file without a message (and vice versa). I think it makes more sense to create a "Communication" table (for lack of a better word). Essentially it allows you to keep track of all types of communication (message and file) for your customers, but also ties message with file (attachments) when appropriate.

table: Communication
communication_id   int
customer_id        int
created            datetime

That would allow you to further normalize by taking "created" and "customer_id" out of the file and message tables. Likewise, you'd want to add "communication_id" to both File and Message. Then to report on them, you could do something like this:

SELECT *
FROM Customer cu
INNER JOIN Communication co ON cu.customer_id = co.customer_id
LEFT JOIN File f ON f.communication_id = co.communication_id
LEFT JOIN Message m ON m.communication_id = co.communication_id
ORDER BY cu.customer_id, co.communication_id

Design Pattern – One of Many Parent Tables

I understand the second solution as not applicable as it does not offer one (object)-to-many (alert) relationship.

You are stuck to just two solutions because of the strict 3NF compliance.

I would design a lesser coupling schema:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  -- See (1)
  -- AlertID     int identity(1,1) not null,

  AlertClass char(1) not null, -- 'P' - Product, 'C' - Customer, 'N' - News
  ForeignKey int not null,
  CreateUTC  datetime2(7) not null,

  -- See (2)
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertClass, ForeignKey)
)

-- (1) you don't need to specify an ID 'just because'. If it's meaningless, just don't.
-- (2) I do believe in composite keys

Or, if integrity relationship shall be mandatory, I might design:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  AlertID     int identity(1,1) not null,
  AlertClass char(1) not null, /* 'P' - Product, 'C' - Customer, 'N' - News */
  CreateUTC  datetime2(7) not null,
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

CREATE TABLE AlertProduct  (AlertID..., ProductID...,  CONSTRAINT FK_AlertProduct_X_Product(ProductID)    REFERENCES Product)
CREATE TABLE AlertCustomer (AlertID..., CustomerID..., CONSTRAINT FK_AlertCustomer_X_Customer(CustomerID) REFERENCES Customer)
CREATE TABLE AlertNews     (AlertID..., NewsID...,     CONSTRAINT FK_AlertNews_X_News(NewsID)             REFERENCES News)

Anyway...

Three valid solutions plus another to be considered for many (objects)-to-one (alert) relationships...

These presented, what's the moral?

They differ subtly, and weight the same on the criterias:

performance on insertions and updatings
complexity on querying
storage space

So, choose that comfier to you.

Best Answer

Related Solutions

Combined or separated tables: File and Message

Design Pattern – One of Many Parent Tables

Related Question