Hi,
Here’s my schema:
create table Client (
id int auto_increment primary key,
name varchar(255) not null
);
create table FeatureMaster (
id int auto_increment primary key,
key varchar(255) not null,
value text not null
);
create table Feature (
id int auto_increment primary key,
ClientID in not null,
FeatureMaster int not null,
value text not null
);
Some test data:
INSERT INTO Client(name) values ('yellowshoe');
INSERT INTO FeatureMaster(key, value) values ('SecurityCode', 'd34gd0ASD#');
INSERT INTO FeatureMaster(key, value) values ('ManageUsers', 'True');
INSERT INTO Feature(ClientID, FeatureMasterID, value) values (1, 2, 'False');
I want to be able to select the value of a feature for a client, the value in FeatureMaster should be returned if no Feature record exists:
This is the type of query I am looking for but don’t know what I need, I’m thinking I need a join between feature and featuremaster.
SELECT value from Feature WHERE ClientID=1 AND key='ManageUsers';
SELECT value from Feature WHERE ClientID=1 AND key='SecurityCode';
Should return ‘False’ and ‘d34gd0ASD#’.
Is this a common pattern? I can think of many uses for a ‘Master’ table that provides defaults and another table has overrides.
Thanks,