Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Question Database design need help

    Good day guys,

    Quick question here.

    I have about 30 fields of information I have to store about each and every user that uses our profile service. Would it be good practice to split the 50 fields into two tables using foreign keys or just one table with all the fields in and which would give the best performance?

    Thanx in advance!
    Later

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Depends on the fields. Performance depends on what you intend to do with the data and how you collect it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    Here are some of the fields I will be using and the split that I have in mind.

    User table
    first_name
    last_name
    title
    email_address
    mobile
    password
    security_q
    security_a

    Profile
    household
    hi_tech
    leisure
    business
    outdoors_diy
    jobs
    vehicles_and_transport
    event
    pets_on_sale
    property_on_sale
    property_for_rent
    sport
    computers
    building_and_construction
    crops_and_plants
    livestock_and_animals
    marketing

    plenty more...

    Any ideas... or advice?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Is everything in Profile supposed to be a bit (yes/no) field?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2011
    Posts
    3
    Most of the fields will be varchar and nvarchar fieldtypes. I have also read that it is better to use varchar instead of nvarchar if you do not require special characters, is that correct?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    It's generally considered better purely for economy of storage. NVARCHAR requires more space.

    The more you can elaborate on what those profile items are, the better. I'm pretty sure they should be broken out but I don't want to say anything definitively without a better picture.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    if you know right here right now that you will never ever add / change or delete categories then I suppose the design you are proposing may make sense. its not one I'd want to use because I just don't trust users not to change things.

    So I'd want (probably) 3 tables
    users

    profiletypes

    id
    descritpion

    userprofiles
    userid
    profileid

    I might be tempted to put in some classificiation / structure ont he profiel types (eg this category 'belongs to' or has a parent category of.... in case the users want to have soem sort of grouping or association

    yes it may be a less efficient in terms of data storage, there may be a worse perfomance on retrival (but I doubt it will be significant)
    however when the user comes at the last minute and demands a new profile you can quietly point them on how they manage their data (add a new profiletype and that automatically appears in the data capture screen). programming time 0, testing time 0, deployment time 0, training time 0. yes it costs a bit more on writing the system, but it does mean you are not going to be bugged by users at the last minutes when sods law dictates you will be under pressure from elsewhere
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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