my-sql-issue.sql

-- I'm trying to write up a schema for an SQLite database storing some data sets
-- Requirements are as follows:
-- 1) Every data set has a unique name
-- 2) Every data set has multiple attributes associated with it
-- 3) Every attribute has several possible values
-- 4) Attributes may be class attributes or non-class attributes
-- 5) Every record is associated with one data set
-- 6) Every record must have exactly one value for every attribute associated with its data set
--
-- Now, I can get as far as point 4 with the following:
CREATE TABLE datasets (
ds_name STRING NOT NULL PRIMARY KEY
);

CREATE TABLE attrs (
attr_name STRING NOT NULL,
is_class INTEGER NOT NULL CHECK (is_class IN (0,1)),
ds_name STRING NOT NULL,
PRIMARY KEY (attr_name, ds_name),
FOREIGN KEY (ds_name) REFERENCES datasets (ds_name)
);

CREATE TABLE attr_vals (
attr_name STRING NOT NULL,
val STRING NOT NULL,
PRIMARY KEY (attr_name, val),
FOREIGN KEY (attr_name) REFERENCES attrs (attr_name)
);

-- But when I get to the records table, I get horribly confused.
-- I don't know what I could possibly use as a primary key in such a table: what sets records apart from each other is
-- their specific combination of attribute values and what data set they belong to. However, since different records will
-- have different attributes and values (depending on which data set they belong to), I assume I'd need a one-to-many, which 
-- would mean a separate table with a foreign key into the records table. However, I need a foreign key to target, bringing me
-- back to my original issue.
--
-- Additionally, how can I possibly guarantee that every record has a value for every attribute that its 'parent' data set
-- is associated with?