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

Depends on the fields. Performance depends on what you intend to do with the data and how you collect it.

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?

Is everything in Profile supposed to be a bit (yes/no) field?

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?

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.

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