I come here to present a problem I've been banging my head on for the last couple of days while developing a design for a database.
A brief introduction:
The database is intended to store information about motortypes (not about actual motors, but more about their design, parameters and such), their components/attributes and relationships between certain motortypes (each motortype is related to a so-called base motortype). So we have several motortypes of which each has many attributes (I call them detailtypes). Each of the detailtypes in turn can be present in one or more motortypes. Each motortype:detailtype combination should have (exactly) one value.
I need to be able to compare the values of a specific detailtype for two different motortypes and store information based on this comparison. (The storage of this information is the database's central purpose.)
Now the first and simplest approach would be to make a motortype table and make it have a column per detailtype so I could store exactly one value per combination:
Approach #1:
CREATE TABLE Motortype(
id INTEGER PRIMARY KEY,
description CHAR (50),
detailtype1 CHAR (50),
detailtype2 INTEGER,
detailtype3 YES/NO,
detailtype4 DOUBLE );
There will be about one hundred (this number is likely to slowly grow in the future) such detailtypes to be entered into the DB. Adding more corresponds to adding more columns (manageable), changing them would result in renaming columns (bad idea?). The problem occurs when I want to compare the motortypes with the IDs 2 and 5 on their detailtype2 values and save information about this comparison. I don't know how one would manage that. (Also the attributes can't have the NOT NULL constraint, as some data might simply be unknown at the time the entry is created...)
That's why I went on to approach 2, which seems to implement EAV in some way.
In order allow storing the comparison informations I moved the detailtypes from being an attribute to mototype to their own entity, while leaving those that need no comparison as attributes:
Approach #2:
CREATE TABLE Motortype(
id INTEGER PRIMARY KEY,
description CHAR (50),
notComparableAttribute1 CHAR (50),
notComparableAttribute2 INTEGER
);
CREATE TABLE Detailtype(
id INTEGER PRIMARY KEY,
description CHAR (50),
datatype CHAR (3)
);
CREATE TABLE MotorDetail(
id INTEGER PRIMARY KEY,
motortype_id INTEGER CONSTRAINT fk_mt_id REFERENCES Motortype(id) NOT NULL,
detailtype_id INTEGER CONSTRAINT fk_dt_id REFERENCES Detailtype(id) NOT NULL,
value CHAR (50) NOT NULL
);
This configuration then allows me to refer to any specific pair of values in MotorDetail and save additional information for this comparison. (Note that there are more dimensions to this comparison - it does not have a 1:1 relation to MotorDetail. Rather it's a 1:n MotorDetail:ComparisonData relation. Otherwise I could just directly store all the comparison information in MotorDetail.)
However, the fact that I now have a value attribute which needs to hold several different data types (strings, integers, floats, booleans) means that the responsibility for input validation is moved from the database to me (I need to make sure the users can only enter the data in a way that makes sense for the respective Detailtype). Which is completely fine - it's just a little more programming work, but I had the feeling it could be solved in a more elegant way.
So after some research on polymorphic associations I came up with the next approach (Motortype and Detailtype are as in #2):
Approach #3:
CREATE TABLE MotorDetail(
id INTEGER PRIMARY KEY,
motortype_id INTEGER CONSTRAINT fk_mt_id REFERENCES Motortype(id) NOT NULL,
detailtype_id INTEGER CONSTRAINT fk_dt_id REFERENCES Detailtype(id) NOT NULL,
valueTxt CHAR (50),
valueInt INTEGER,
valueDbl DOUBLE,
valueBoo YES/NO
);
The value attributes can't have NOT NULL constraints, as 75% of the values will be null (as per requirements there can only be one value for each MotorDetail). Querying for values seems ok as long as I only need those of a specific data type, otherwise it gets more complicated (e.g. to display them on a form). Or maybe I should say tedious instead of complicated.
So in a last attempt at the problem I present my final approach (again, Motortype and Detailtype as in #2):
Approach #4:
CREATE TABLE MotorDetail(
id INTEGER PRIMARY KEY,
motortype_id INTEGER CONSTRAINT fk_mt_id REFERENCES Motortype(id) NOT NULL,
detailtype_id INTEGER CONSTRAINT fk_dt_id REFERENCES Detailtype(id) NOT NULL
);
CREATE TABLE MotorDetailValueTxt(
id INTEGER PRIMARY KEY,
valueTxt CHAR (50)
);
CREATE TABLE MotorDetailValueInt(
id INTEGER PRIMARY KEY,
valueTxt INTEGER
);
... (same for MotorDetailValueDbl and MotorDetailValueBoo)
I'm not sure on this one. Not sure how to represent the idea that each id in the value tables is tied to exactly one MotorDetail(id) and needs to be unique across all value tables. Not even sure I understood the underlying concept fully. But what I'm sure of is that doing it this way the DB can't enforce referential integrity. That's why I'm not going to use it. In a test DB I was able to produce a query with UNION to get all the different values, but I don't think I want to do any housekeeping down at table entry level (to somehow ensure no dead entries remain).
The question:
Given the details outlined so far, is there a possible solution that does not involve some coding wizardry of some kind (VBA or otherwise)? An elegant solution that somehow allows handling this all at the database level (tables+relations as opposed to reports, forms, etc...)? As everything I've come up so far seems rather clunky.
Note: I am quite the newcomer to database design and databases in general. I've had 3 days of training in MS Access, but that was mostly tailored towards learning the GUI and building some really basic DBs. About anything else I know (or assume to know) about DB development was learned by reading blogs - and most importantly: Questions and answers here on SO.
It seems safe to assume that I did not (fully) understand one or more of the concepts I discussed and in consequence misrepresented some of it. If so, or if anything else is missing, please point it out to me in the comments or make an edit to improve my question (and to allow me to read up on it). :)
Edit: An edit was made, and a large one a that. I tried to add information, clarify what was there and still keep the original character and intent of the question. Let's hope I succeeded at least partially. :)
