relationships between n tables and a document table

Question

relationships between n tables and a document table

dg78

SSC Veteran

Points: 296
More actions
August 7, 2005 at 3:19 am

#168258

Hi ,

In a database, i have n tables :
- Customer : CustomerID, name ..
- Supplier : SupplierID, name ..
- Employee : EmployeeID, name ..
- Product : ProductID, name ..
- Engine : EngineID, name ..
...

and a table for documents :
- Document : DocumentID, name, resume, type (.doc, .pdf ..), text or path (c:\dirname\filename.ext), ...

My question is about relationships between the n tables and the document table.

Each row of each entity can have many documents and a document is only for one row in one entity.

I see three solutions :

1) to have one document table for each entity :
- DocumentCustomer : DocID, CustomerID (FK), name, resume ...
- DocumentSupplier : DocID, SupplierID (FK), name, resume ...

there are n Document tables.

2) only one Document table and n joins tables :
- Document : DocumentID, name, resume,
- CustomerDocument : DocumentID, CustomerID
- SupplierDocument : DocumentID, SupplierID
- EmployeeDocument : DocumentID, EmployeeID
..

3) only one Document table with n foreign keys :
- Document : DocumentID, CustomerID (FK), SupplierID (FK), EmployeeID (FK), ..., name, resume ..

All foreign keys can be null in the schema of the table.
In a record, only one foreign key is not null, the others are null (it is possible to put a constraint check for that) or all foreign keys are null if the document is not still affected to a row of an entity.

Now questions :
- what is the best solutions (1, 2 or 3) ?
- are there another solutions ?
- is there a quicker iterative constraint (for solution 3) other than :
((CustomerID is not null) and (SupplierID is null or EmployeeID is null or ...)) or ((SupplierID is not null and (CustomerID is null or ...)) ..

I prefer solution 3 if a document is only for one row of one entity (only one table instead of n or n+1 tables).
If a document is also for many rows of entities, i prefer solution 2.
And you, what do you prefer ?

I want to use that in stored procedures called by VB.NET and ADO.NET (i can test values in vb.net before to call stored proc).

Thanks in advance

Dominique Gratpain

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 1

I prefer 2.

The reasoning to me is that this solves your issue, but gives you flexibility for the future. It's easy to say in business, one doc per customer, but then you'll get merges, buyouts, or the rules change and you want two customers to shared or reference one document. If you've designed as 3, you might have issues.

dg78 SSC Veteran Points: 296 More actions · Answer 2

Thanks Steve for your answer.

It is true that solution 2 is better for the future.

Now imagine i replace the Document table by an Address Table.

Address table : AddressID, type (invoice, shipping, personal ..), street, zip cope, city, region ..

The N tables are : Customer, Supplier, Employee, Contact ..

An address is unique for one customer or one supplier or ..

It is the same for a phone table or an e-mail table.

What do you think about solution 3 ?

There is one table instead of N (solution 1) or N+1 (solution 2) tables.

I think the less number of tables there are, the better it is.

Dominique