ER design

Question

Hello Rooms,

I have a table that will only storage information as follows:

·         Project

·         Non-Project

If Project was selected, it should link to Project tables.

If Non-Project selected, it should link to DocumentType table.

Can someone give me some hints how to design on ER?

Many thanks.

---

None

Answer 1

I can think of two simple ways off the top of my head.

#1.  Create a base table that will hold Projects and Non-projects (I'll call it "Workflow").  This Workflow table stores information that would be applicable to both Projects and Non-Projects, and would also have two columns - the ProjectID and the NonProjectID.  These are foreign keys to the Project and DocumentType tables.  Additionally, add a CHECK constraint to ensure that if ProjectID is NOT NULL, then DocumentTypeID must be NULL, and vice versa.

#2.  Create two separate tables, Project and NonProject.  Create a view over the top of these, so you can query both at once.  Eg:

CREATE VIEW Workflow AS

SELECT 'Project' AS WorkflowType, <columns from Projects> FROM Projects

UNION

SELECT 'NonProject' AS WorkflowType, <columns from DocumentType> FROM DocumentType

Without knowing more about your design, I can't say which method I prefer more.  #1 is good, but it doesn't scale very well - but if you will only have two different types, Project and Non-Projects, it shouldn't matter.