Database design problem

[padders]

I am wondering if someone can offer my a suggestion for my database design.

I am trying to build a script that allows the admin to set up custom fields.

For example, i want to have a user table with as standard

ID
username
password
email

but then in admin i want the admin to be able to add any field they want. So they could then go and add:

- Location
- Website

etc... (and also remove them).

I will then make the script build add.php forms based on the fieldnames in the table, all no problem.


but.....

what i also want is to be able to assign some "settings" to those custom fieldnames. "Required" is an obvious one as is "user_editable" (so that the admin can set a record which a user can't change).

Thing is, as far as the database design goes i have no clue how to do this.

A thought was having a table like

FieldID required user_editable

sort of thing, but how can i link up "fieldid" with the field from user? Is this even possible?

would be real grateful for suggestions to my little problem!

[Skunk]

Hmmm... how about this:

Users table
===========
ID 
username 
password 
email 

CustomFields Table
==================
ID
Name
Required
user_editable

UserFields Table
================
UserID
FieldID
Value

The CustomFields table stores information on the custom fields that you have defined.  
When a user fills in a custom field an entry is added to the UserFields table, with 
the User's ID, the custom field's ID and the value the user entered.

The only thing to remember is that when you delete a custom field you should delete all entries in the UserFields table for that custom field.

Sorted

[padders]

Thank you, i think that is a similair way to how vbulletin works but i think in vb you get a max of 3 custom fields and is not as flexible as the solution you have offered.

That looks like the way to do it, i can see the queries i am going to need forming in my head.

Thanks a lot for the help.

[MattR]

<edit>
Nope, vB *used* to be bad but they have fixed it I think.
</edit>

[padders]

Originally posted by MattR
<edit>
Nope, vB *used* to be bad but they have fixed it I think.
</edit>

hehe, i really should upgrade from beta3, just lazy

[MattR]

I edited my post too soon.

They are still using the bad, Bad, BAD!! version of:

Userfield
==========
UserID,
UserField1,
UserField2,
UserField3,
UserField4

That's just... That's just bad programming, mmkay!

Skunk's way is the way to go if you're doing it. Heavens knows why they did it that way (maybe a performance increase? Hard to tell but it would have to be offset by the fact that you have all these extra CHAR( 250 ) fields laying around taking up space even if they're empty).

[padders]

yeah i thought so

i have also thought of an alternative to do what i want. This is what links SQL do: (gossamer-threads, mega recommended!)

In their case the table is link and you can set and modify the column you create. Instead of doing this is a datbase, a flat file "link.def" file is created. An example of one record in this file is:

'URL' => {
'type' => 'CHAR',
'pos' => '3',
'form_type' => 'TEXT',
'default' => 'http://',
'weight' => '1',
'size' => '255',
'not_null' => '1',
'regex' => '^(https?:\/\/|ftp:\/\/|news:\/\/|mailto',
'form_size' => '20',

},

this this creates the array containing all the details that would be used in creation and validation of user input based on this field.

What do people think of this as a solution, one reason i quite like it is it means all the user data is kept in one table and easier to manage. The first solution is clever and works well but the table:

User ID | Field ID | Data

is not quite as easy to manage visually than a nice organized user table.

What do you think?

[padders]

I have had another thought on this, i don't think this is the best database design but is it really bad?


To have table user

this has all the columns in it that the admin control panel makes/deletes.

And then have another column with title field_properties or something like:

ID
Field_Name
is_Required
is_Editable

sort of thing?

that might make it easier? is this a bad way to do it do you think?

We are not going to have wasted space as an issue really, the reason for doing it this way is so it can be easily modified for different people, if it was just me using it i could easily set up the user table for just the fields i need and set the validation manually in the code but because i want to be flexible need to do it another way.

what do you think, which is the best of the 3 options?

[padders]

i might be talking to myself here a bit... but never the less.

one of the things i want to do is allow for a regex for each field name as well as setting if the field can be updated by the user (as opposed to admin).

If i use the last option and have the two tables, one for user and one for settings with latter having structure

ID | User_Editable | Regex

After the user submits the form i do something lile:

$query = SELECT from settings WHERE User_Editable = 'Y'

start loop through
and set:

$fieldname == "$array['fieldname'];
$regex == "$array['regex'];

now i know that the fields submitted by the user from the form will have same name as fieldname (if not they are an error so we don't want to use them anyway)

so i do:

// we have got all the fieldnames that are user_editable sent to us. we are now going to go through them each one by one and make sure that the fields do not fail the regex. If they do we build an error message, if they do not
we build the SQL statement to insert.
if (ereg ("$regex", $$fieldname)) {
    $query .= ", $fieldname == '$$fieldname'"
    } else {
$error .= "<b>$fieldname value missing or invalid</b>";
$continue = 0;
} 

now just to work out how best to store the regex in the database...