Holding config data in a table
Posted by John_Moylan (jfp), 13 December 2004I'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)
I need to hold configuration data in a table and was considering a Key => Value hash type scenario
A Group *will* have the data
I may need a different message for *some* Users
A User *could* have the data
I may also need a different message for *some* Endusers that overides both the Group & Users config
An Enduser *could* have the data
Is there a recognised *best way* of doing this in one table?
My initial thoughts were a table like this
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
So I try
So finally I try
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
Posted by Custard (Custard), 13 December 2004Hi 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.
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.
Posted by John_Moylan (jfp), 13 December 2004Hello 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.
Posted by Custard (Custard), 13 December 2004Hi
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.
Posted by admin (Graham Ellis), 14 December 2004My 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" ...
PH: 01225 708225 • FAX: 01225 793803 • EMAIL: firstname.lastname@example.org • WEB: http://www.wellho.net • SKYPE: wellho