SQL Server Database Design – One Table or Duplicate Table with Different Dependencies

This looks like the party relationship model (see https://stackoverflow.com/questions/4969133/database-design-articles-blog-posts-photos-stories).

I recently went through something similar, combining different content types under a single table with subtables for each unique content type.

I'm not sure about the entity framework, but in the new model you have, you'll have an easier way to link content to other content by having a central place storing the ids/primary keys. It's a common design pattern, so I don't think there's anything "inherently stupid" about it.

I don't think you needed to worry about the first one, but the second one is obviously better thought out.

This part in Item doesn't look right:

F.K = JID, OID
  JID REFERENCES PURCHASE (JID)
  OID REFERENCES PURCHASE (OID)

I suppose the design is only on paper, as no DBMS would allow that, when the primary key of PURCHASE is (JID, OID). You need one (composite) Foreign Key constraint, not two:

  (JID, OID) REFERENCES PURCHASE (JID, OID)

Now this foreign key belong to Puschase_Materials (or Purchace_Items) and not to Materials (or Item). With that in mind, the corrected schema would be:

PurchaseOrders (JID, OID, ODate)
PK = (JID, OID)
FK1 = (JID) REFERENCES Jobs (JID)

Items (IID, Stock, Description, Price)
PK = IID

Purchases_Items (IID, JID, OID)
PK = (IID, JID, OID)
FK1 = (IID) REFERENCES Items (IID)
FK2 = (JID, OID) REFERENCES PurchaseOrders (JID, OID)

The image shows an additional relationship, between Purchase_Materials and Jobs/PrintingJobs, which doesn't appear in the schema - and you don't really need it.

Since there is a foreign key from Purchase_Items to PurchaseOrders (or Purchase_Orders in the image) and a foreign key from PurchaseOrders to Jobs, you don't need a direct foreign key. Consistency is enforced through this indirect relationship.

• There is inconsistencies in the naming, between the schema and the images. I assumed the following are renames:

  Schema         - Image              - Code in this answer
  ---------------------------------------------------------
  Jobs           - PrintingJobs       - Jobs
  PurchaseOrders - Purchase           - PurchaseOrders
  Item           - Materials          - Items
    (none)       - Purchase_Materials - Purchases_Items
  

• Some table names are plural and others singular.

• Some names have underscores between name parts, some not.

It would be best if you fixed those and decide on a naming convention (say all table names are plural, there are no underscores except in case X, etc).