My current db design is in the screenshot. It seems flawed because there is no enforced relationship (pk/fk) between the t_criteria_director and the t_criteria_a b c d e f... tables. Is there a better way to design this one to many scenario to keep referential integrity?
-
it seems to me you want a standard one-to-many relationship to a table with only value_a mandatory and possibly other integrity constraints. Otherwise you can always "simulate" one-to-many relationships to force FK constraints, though one-to-one relationships tend to be tedious and fragmenting. – DevilSuichiro Dec 08 '17 at 05:23
-
Please [use text, not images/links, for text](https://meta.stackoverflow.com/q/285551/3404097)--in this case, for all the content of this diagram. – philipxy Dec 09 '17 at 03:22
-
Thanks for info, I learned something new today. Next post I will not add an image. – KeelRisk Dec 09 '17 at 05:07
-
1Are the relationships between criteria director and the various criteria tables IS-A type relationships? You haven't described the subject matter you are trying to model. – Walter Mitty Dec 10 '17 at 07:48
1 Answers
Your design doesn't "seem" anything because we can't read your mind. You have given some aspects of a design but not the business "scenario" it represents/implements/describes or how it does so.
SQL NULL, UNIQUE, PKs & FKs are kinds of constraints. A constraint is a limitation on what database values can appear. An SQL FK says subrow values for a column list in a table must appear elsewhere for a column list whose columns form a SQL UNIQUE NOT NULL column set (which PK is a case of) in their table. If your design is subject to a constraint and it isn't implied by other enforced constraints, enforce it. Otherwise don't. Preferably declaratively. Most SQL DBMSs only let you declare a few kinds of cheap-to-enforce constraints. Others must be enforced via triggers.
The constraints are a consequence of the criteria for rows going into vs staying out of tables in a given situation (the table predicates, "what the tables mean") and what situations can & can't arise according to your business rules. We don't know what those are unless you tell us. We can hope to guess using your table & column names, any other info you give & common sense.
You have to tell us either the constraints or the predicates & what situations can/can't arise.
Here your tables are using a straightforward table plus some EAV design to record the data of some straightforward table not explicitly in your design. As always you could avoid EAV by just using DDL to keep the straightforward table's schema & constraints up to date, but instead you have chosen a static schema that requires more complex schema, queries & constraints. The straightforward expression of the EAV constraints is typically that the straightforward table they represent has certain constraints plus that the t_criteria_x are views of it and/or that it is a view of them. But typically the only SQL declarations available just let you express fragments of that.
I guess that what you intend here includes that for each t_criteria_x table its PK value must appear in t_criteria_director with a table_name value 't_criteria_x'. Another way to put this is that if you added to t_criteria_x a table_name column with value 't_criteria_x' then the result must have (id, table_name) subrows appear as t_criteria_director (criteria_id, table_name) subrows. If also t_criteria_director (criteria_id, table_name) subrows are SQL UNIQUE NOT NULL then we have that that augmented t_criteria_x has a composite SQL FK (id, table_name) referencing t_criteria_director (criteria_id, table_name). You can express this declaratively by actually augmenting t_criteria_x by such a (possibly computed/generated/calculated) columns. But you probably also have other constraints, like there aren't any (constraint_id, table_name) pairs in t_criteria_director that aren't referenced by some augmented t_constraint_x.
Calling the column table_name shows an implementation-oriented bias from the EAV because that column is a type/variant discriminator/tag in the ER sense that the types of entities represented by the ids in the t_criteria_x tables are "subtypes" of the type of entity represented by t_criteria_director. (This is also a concept/technique from 3GL record data structures used for dynamically simulating typing.) After all the table_name column value doesn't have to be a table name, it just has to be some value that identifies the entity subtype, and such entities don't have to only participate in one table's relationship/association. (Research SQL/database/ER subtyping/polymorphism/inheritance and the design anti-pattern two/many/multiple FKs [sic] to two/many/multiple tables.)
You have to determine what the table predicates are and what their constraints consequently are. Preferably via determining what the straightforward table they collectively represent is & what its predicate is and what the database constraints are using it. Then you must decide whether per cost/benefit you are going to modify your design to make constraints declarative and/or you are or are not going to enforce constraints via triggers.

- 14,867
- 6
- 39
- 83
-
I have decided to use ONE criteria table that will contain a json field and therefore I will not need potentially hundreds of criteria_a,b,c... tables. In this particular case this works perfectly because none of those fields will need to be queried. – KeelRisk Dec 09 '17 at 05:14
-
If you ever do have some aribtrarily deep tree-structure relationship(s) and want to generically/relationally query its parts (eg if you started with JSON data of arbitrary depth), read about CTE queries & [heirarchical data](https://stackoverflow.com/q/4048151/3404097). – philipxy Dec 09 '17 at 05:23