I'm designing a system that will have 3 types of user, for arguement sake I'll call them Groups->Users->Enduser who all have an id. (not unique across the 3 users types as they are separate tables with ID's that auto increment)
In this case, 'Groups' have 'Users' who in turn have 'Endusers' so I can access the User an Enduser belongs to by the Enduser_id (I'm stating the obvious for a reason which I hope will clarify the question below)

Group = My Customer (e.g ACME Grocery Distribution )
Users = Thier customer (e.g. Tescos who are a customer of ACME)
Enduser = Mavis (e.g. Mavis in accounts at Tescos)
--------------------
Question
--------------------
I need to hold configuration data in a table and was considering a Key => Value hash type scenario

For example:
A Group *will* have the data
  Code:

Key = 'Message'  Value = 'Hello to Group'

I may need a different message for *some* Users
A User *could* have the data
  Code:

Key = 'Message'  Value = 'Salutations to Users'

I may also need a different message for *some* Endusers that overides both the Group & Users config
An Enduser *could* have the data
  Code:

Key = 'Message'  Value = 'Greetings Enduser'

Is there a recognised *best way* of doing this in one table?

My initial thoughts were a table like this
Code:

create table configuration (  group_id int(11)  users_id(11) Default Null,  enduser_id() Default NULL,  configuration_key varchar(64) not null ,  configuration_value varchar(255) not null ,  PRIMARY KEY (configuration_id) );

Assume already know that group_id = 112, users_id = 345 and enduser_id = 112 (same as Group_id, I know)

This way I can query Endsuser_id "Message Key => Value" if its null then try the Usersid_id Key => Value that the Enduser

example
Code:

SELECT configuration_value FROM configuration WHERE configuration_key = 'MESSAGE' AND enduser_id = 112;  No results found

So I try
Code:

SELECT configuration_value FROM configuration WHERE configuration_key = 'MESSAGE' AND user_id = 112;    No results found

     
So finally I try
Code:

SELECT configuration_value FROM configuration WHERE configuration_key = 'MESSAGE' AND group_id = 112;  This returns results

Now that feels wrong to me.
Should I be enforcing unique ids across all three types? that seems to me to be like the answer, then I only have one id in the config table, and I know it only resolves to one instance of a user

I really should buy a book on Database Design
Any comments/tips and random thoughts gratefully received

jfp

Hi there.

Since you appear to want an individual salutation for each user, why not keep a separate field in the user record for it.
Otherwise you end up with a bit of a complicated configuration table.
And you're not wasting any more space.

Also, it's possible you could redesign slightly and have a single 'users' table and a new table called 'userType' or something. Then you retain some flexibility if a new type of user comes along.

Code:

userType -----------   id type /\ | | user ---------------- id typeId parentUserId salutation

I think that's right anyway..
Then you still get to find out whether a user belongs to a larger user (like Tescos) and Tescos can be a user of Acme. The root of the tree has a null parent.

HTH

B

Hello Custard

I worried that putting everything in a user table will lead to a bloated table, the configuration values are for a Java applet that should get more feature rich as new release come out, that means more params. I wanted to separate the params from the user data so I could plug in new features with ease.

I think I'm going to follow your advice with the single user table and map the usertype from another table, that will leave me with a unique user_id for every usertype to use in a config table.

Thanks for the input.

jfp

Hi

You could perhaps have a default salutation in your configuration table, and fill in the specialised salutation in the user table.
AFAICR it a VARCHAR won't take up (much) space if it is empty.

Either that, or if there is a list of stock salutations, you can create a separate salutation table for them and index from user appropriately. (Codd would be proud.. ) But maybe that's going too far.

B

My brain isn't functioning yet this morning ... but I would be inclined to think of keeping:
* A full single table if all the user salutations were different and you expected most to have a salutation
* A salutation table (with a sid to join to it in the main user table)  if there were standard salutations OR if only a tiny proportion of users had salutations.

If you are going to have performance issues (i.e. if you measure accesses in hits per second) and you only want the salutations occasionally when you look the users up, that would also be a good reason to put them in a separate table even if everyone is going to have their own tailored greeting.  "Denormalised" ...

---