Master table with defaults - join with override table

Databases
1

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,

2

Then I will do the same. Thanks again,

3

Thanks Rudy!

I’ve renamed key to sKey, do you prefix your columns in a standard way?

4

no, i don’t

i try to use short but accurate and descriptive names, that (1) aren’t reserved words, and (2) contain no special characters

5 
SELECT fm.`KEY`
     , COALESCE(f.value,fm.value) AS value
  FROM FeatureMaster AS fm
LEFT OUTER
  JOIN Feature AS f
    ON f.ClientID=1
   AND f.FeatureMaster = fm.id
 WHERE fm.`KEY` IN ( 'ManageUsers'
                   , 'SecurityCode' )

please rename your KEY column as KEY is a reserved word and requires those horrid backticks