2

Background:
Two projects (A & B) under design at the same time both needs a new table(called DocumentStore) to store document/file under postgres.

But business logic around the document storage are different between project A & B, this means relationship around DocumentStore are different between A & B.

Let's make this a bit more concrete, see example below:

The Document storage table structure looks the same without constraints/ foreign Keys:

Table DocumentStore

DocUUID //unique Id for this document, PK, FK to other table depends on project 
fileName //file name
fileType //file type
FileContent //store file as blog

In project A, DocumentStore.DocUUID references Email.EmailUUID:

Note there is a one to many relationship between Email -> DocumentStore via the FK.

Table Email

EmailUUID //PK
subject
title
...

In project B, DocumentStore.DocUUID references Letter.LetterUUID:

Note there is a one to many relationship between Letter -> DocumentStore via the FK.

Table Letter

LetterUUID //PK
UserId
rightId
...

Email and Letter are completely different because of different of business logic.

My questions are:

  1. Should I share this DocumentStore table between project A & B ?

  2. If the answer to 1. is yes, then how? Through inheritance under postgres?

  3. If the answer to 1. is no, should I create two table with the same structure but different table name and different foreign key ? One for project A and project B?

RoundPi
  • 5,819
  • 7
  • 49
  • 75
  • The core question is: are the same documents linked to email *and* letter often? How often? – Erwin Brandstetter May 13 '14 at 21:03
  • @ErwinBrandstetter: different document links to email & letter. It's gonna be more often for letter case(perhaps most cases), and less often for email case(maybe 2/10 cases). I am not sure why this is the core question ? Could you please elaborate ? – RoundPi May 13 '14 at 21:19
  • If individual rows in documents are not shared between emails and letters, you'd better have separate tables to begin with. Your answer is not clear to me. I can't parse `different document links to email & letter` unambiguously. – Erwin Brandstetter May 13 '14 at 21:39
  • I don't think any individual row in document would be shared between emails & letters. Any row should either belong to Letter or Email. I think my question is quite clear as with the example above, the only thing similar is the table structure of document, but unfortunately it seems I cannot easily reuse this document table as the FK of DocUUID can only be tight to one other table column. Is this clear enough ? – RoundPi May 13 '14 at 21:47
  • Updated answer accordingly. – Erwin Brandstetter May 13 '14 at 22:11

2 Answers2

3

Only one of those fk constraints works per column of the same instance of the table. You would have to add one column for each fk. Or have two documentstore tables.

As you clarified, the same row in documentstore belongs to either a letter or an email, but only to a single one of those, while each letter / email can have multiple documents.
Hence my new advice: stick with table design you have now, but create two separate tables. There is no gain in having them in the same table. The fact that both tables share the same structure is no good reason to share the data.

You can have schema_a.documentstore and schema_b.documentstore inheriting from master.documentstore. That would mainly be useful if you have use cases dealing with all rows in both tables at once. Be sure to read the chapter about limitations of inheritance in Postgres. In particular, it won't allow you to define a single fk constraint:

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint.

Related answers with code examples:
Find out which schema based on table values
Create a table of two types in PostgreSQL

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for the answer but there is a one to many mapping from Email to DocumentStore table via the DocUUID. If I were to include DocUUID as foreign key in letter and email, it would introduce redundant information in Email & Letter... – RoundPi May 13 '14 at 20:48
  • Could you show me how does inheritance help here in terms of the one to many mapping ? – RoundPi May 13 '14 at 20:52
  • I have 1:m relationship between letter and Doc and another 1:n relationship between email and Doc. Does that make n:m relationship between all those ? – RoundPi May 13 '14 at 21:20
  • could you please give some example code for my case if you think your answer could solve my problem. Thanks – RoundPi May 13 '14 at 21:23
  • @Gob00st: I suspect that a Doc can also be used in multiple letters / emails. That would make it an n:m relationship. "One letter can have multiple docs, one doc can be used in multiple letters." Are you sure you don't need that? – Erwin Brandstetter May 13 '14 at 21:32
  • A Doc can only be used in one email or letter. I am sure I don't need a doc can be used in more than one email or letter. – RoundPi May 15 '14 at 11:57
0

same table is fine. then you have some choice - you may add a 'type' column if you need to differntiate inside the same table - which i think you don't really need or you build associative classes to the other things like this:

Doc_email
----------
DocUUID
EmailUUID

and

Doc_letter
-----------
DocUUID
LetterUUID
Randy
  • 16,480
  • 1
  • 37
  • 55
  • So you are against having a extra enum type in the DocumentStore table ? And what do you mean associative classes ? Can you elaborate your example a bit more ? Thanks – RoundPi May 13 '14 at 20:55
  • associative tables link two other atomic tables together without impacting either one. For the enum, I dont find that is typically required. Although you have not specified your business rules, which may be some kind of special case. – Randy May 14 '14 at 00:48