Good design: 2 tables with a foreign key to the same primary key

Question

I wonder whether I am following a good design for following db tables.

Tables:

• Project_Proposal
• Milestones_Proposal
• Resources_Proposal
• Project
• Milestones_Project
• Resources_Project

Both milestones tables have the same columns and both resource tables have the same columns.

My question is whether I should

1. Merge Milestones_Proposal with Milestones_Project
2. Merge Resources_Proposal with Resources_Project
3. Create a column "TYPE" to identify if the data in these tables belong to either a project or a project proposal
4. Define a foreign key in Milestones and Resources that either point to Project_Proposal or Project table depending on the "Type" value.

Design would be then like this:

• Project_Proposal
• Project
• Milestones
• Resources

Many thanks, Peter

Answer 1

You have the same fields, but they "point" (through foreign keys) to different tables, so you can't just merge them into a single field. What you'll end-up with is a CHECK that allows one field or the other to contain a non-NULL value, based on the type field.

Which is perfectly fine in this simple case. You are essentially following the strategy #1 out of the 3 possible strategies for implementing inheritance.