Search code examples
sqlmysqldatabasetheory

Database Design for One to One relationships

I'm trying to finalize my design of the data model for my project, and am having difficulty figuring out which way to go with it.

I have a table of users, and an undetermined number of attributes that apply to that user. The attributes are in almost every case optional, so null values are allowed. Each of these attributes are one to one for the user. Should I put them on the same table, and keep adding columns when attributes are added (making the user table quite wide), or should I put each attribute on a separate table with a foreign key to the user table.

I have decided against using the EAV model.

Thanks!

Edit

Properties include thing like marital status, gender, age, first and last name, occupation, etc. All are optional.

Solution

  • Could you give some examples of what kind of properties you'd want to add to the user table? As long as you stay below roughly 50 columns, it shouldn't be a big deal.

    How ever, one way would be to split the data:

    One table (users) for username, hashed_password, last_login, last_ip, current_ip etc, another table (profiles) for display_name, birth_day etc.

    You'd link them either via the same id property or you'd add an user_id column to the other tables.