Have Geek Brain

Database design for one-to-one relationships

I am trying to complete my data model project for my project and I am having a hard time figuring out which way to go with it.

I have a users table and an undefined number of attributes that apply to that user. Attributes are optional in almost every case, so null values ​​are allowed. Each of these attributes is one for one for the user. Should I put them in the same table and add columns when adding attributes (which makes the user table quite wide), or should I put each attribute in a separate foreign key table in the user table.

I decided not to use the EAV model.

Thanks!

Edit

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

+2


a source to share


4 answers


Could you provide some examples of what properties you want to add to the users table? As long as you stay below about 50 columns, this shouldn't be a big problem.

However, 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 will link them either using the same id property, or add the user_id column to other tables.

+2


a source


Tables:

  • USERS
  • USER_PREFERENCE_TYPE_CODES
  • USER_PREFERENCES


USER_PREFERENCES

- a many-to-many table joining tables USERS

and USER_PREFERENCE_TYPE_CODES

. This will allow you to normalize the preference type attribute while retaining the flexibility to add preferences without having to use the ALTER TABLE statement.

+3


a source


It depends.

You need to see what percentage of users will have this attribute. If the attribute is "WalkedOnTheMoon" then strip it, if it's "Sex" include it in the user table. Also consider the number of columns on the base table, a few, 10-20 won't hurt that much.

If you have several related attributes, you can group them into a common table: "MedicalSchoolId", "MedicalSpeciality", "ResidencyHospitalId", etc. can be merged into the UserMedical table.

+2


a source


Personally, I would determine if natural attribute groupings exist. You can most often query on a user table and others on a separate table with a one-to-one relationship so that the table is not too wide (we usually call this something like User_Extended). If some attributes fall into natural groupings, they may call a separate table, because these attributes are usually queried together.

Examining the attributes, examine if it is possible to combine some of them into one column (for example, if the user cannot simlutaneoulsy be three different things (say trainee, resident, attending), but only one of them at a time, it is better to have one field and put into it the data, not the three bitfields that need to be translated. This is especially true if you need to use a case statement with all three files to get the information (like the name) you want In other words, look at your attributes and see whether they are truly separate or can be classified as more general.

+2


a source






More articles:


All Articles