SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  2. #2
    SitePoint Columnist Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm... how about this:
    Code:
    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

  3. #3
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <edit>
    Nope, vB *used* to be bad but they have fixed it I think.
    </edit>
    Last edited by MattR; Apr 17, 2001 at 01:31.

  5. #5
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).
    Last edited by MattR; Apr 17, 2001 at 02:05.

  7. #7
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  8. #8
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  9. #9
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    // 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...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •