Best practices and/or advice for diamond relationing tables (Linq to SQL)

Answer 1

Your red arrows imply that a product could not have any rows in IngredientProperties unless the product had at least one matching row in ProductProperties. Does it make sense that a product's ingredients can't have a density until the product has a density? This restriction does not exist with only the "black arrow" FKs.

(Does it even make sense to repeat the properties for each product? Does the density, opacity or price of ingredient A1 vary depending on which product it's incorporated into?)

The FK from IngredientProperties to Ingredients makes sense, but it should be a single aggregate key, not two separate FKs, which implies that the PK on Ingredients should also be an aggregate key, and the same applies to ProductProperties.

Edit:

Thanks for the update. As I implied earlier, adding Foreign Keys to match the red/green arrows in your diagram will create constraints within the database that do not currently exist, which in turn could break existing code that uses the database if it, e.g., inserts into ProductProperties before inserting ingredients.

By using an aggregate key, you are basically saying that only one (ProdID, IngredientID) can exist in the Ingredients table. It looks like that's already being done. If the circled header items indicate indexes, then the data is already well-indexed.

I suspect that the "top" red arrow is incorrect, actually. There are two PropertyKey rows, but I don't think they represent the same thing, since there's a separate PropertyValue in each table. One pair represents properties of products, the other properties of ingredients, so linking them together will just cause confusion.

I'm still not 100% sure what you're looking for, but here are my recommendations:

1. Set up (or keep) PKs/indexes on each table as represented by the circled header items.
2. Set up FKs to match the black arrows from Ingredients and PropductProperties.
3. Set up FKs to match the green arrows.

The indexes are all that's needed for efficient join queries between tables. The foreign keys serve to maintain "referential integrity." For example, they prevent you from inserting properties for an ingredient that doesn't exist.

There are several things you could do to normalize this database as well, but I would not change it unless you have specific problems that need to be fixed.

Answer 2

It looks like you have an Entity-Attribute-Value design in your IngredientProperties table. These kinds of designs can be downright dangerous if you're not ludicrously meticulous when creating your constraints. From what you've told about the problem at hand so far, you are free to change the schema, and you haven't specified a need to support arbitrary properties.

If the list of properties is, indeed, pre-defined and will rarely change, I would turn them into columns, for a schema that would look something like this, taking some liberties with style:

CREATE TABLE Ingredients(
 ProductID int IDENTITY NOT NULL PRIMARY KEY,
 SerialNum varchar(15) NOT NULL UNIQUE,
 Opacity int,
 Density int
);

CREATE TABLE Ingredients(
 IngredientID int IDENTITY NOT NULL PRIMARY KEY,
 ProductID int NOT NULL FOREIGN KEY REFERENCES Products (ProductID),
 SerialNum varchar(15) NOT NULL UNIQUE,
 Price money NOT NULL,
 Opacity int,
 Density int
);

This does not, of course, answer your question directly. Instead, it makes the problem go away. If the assumptions outlined above are correct, this should meet your needs, and be much more pleasant to work with.