I am attempting to finalize my style of the information model for my project, and am getting difficulty determining which approach to take by using it.
I've got a table of customers, as well as an undetermined quantity of characteristics that affect that user. The characteristics have been in nearly every situation optional, so null values are permitted. All these characteristics are face to face for that user. Must I use them exactly the same table, and adding posts when characteristics are added (making the consumer table quite wide), or must i put each attribute on the separate table having a foreign answer to the consumer table.
I've made the decision against while using EAV model.
Qualities include factor like marital status, gender, age, first and surname, occupation, etc. Each one is optional.
Would you give some good examples of the items type of qualities you would increase the user table? As lengthy while you stay below roughly 50 posts, it should not be considered a large deal.
How ever, one of the ways is always to split the information:
One table (customers) for username, hashed_password, last_login, last_ip, current_ip etc, another table (profiles) for display_title, birth_day etc.
You'd link them either through the same id property or you'd add an user_id column towards the other tables.
It is dependent.
You have to Take a look at what number of customers may have that attribute. When the attribute is 'WalkedOnTheMoon' then split it, if it's 'Sex' include it around the user's table. Also consider the amount of posts around the base table, a couple of, 10-20, will not hurt much.
For those who have several related characteristics you can group them right into a common table: 'MedicalSchoolId', 'MedicalSpeciality', 'ResidencyHospitalId', etc. might be combined in UserMedical table.
USER_PREFERENCES is really a many-to-many table, hooking up the
USER_PREFERENCE_TYPE_CODES tables. This will help you to normalize the preference type attribute, yet still be flexible to include preferences without requiring an ALTER TABLE statement.
Personally I'd choose whether you will find natural types of characteristics. You may place the most generally queried within the user table and also the others inside a separate table having a one-to-one relationship to help keep the table from being too wide (we usually call that something similar to User_Extended). If a few of the characteristics fall under natural types, they might require a separate table because individuals characteristics will often be queried together.
In searching in the characteristics, examine if some could be combined into one column (for example if your user cannot simlutaneoulsy be three differnt things (say intern, resident, attending) only one of these at any given time, it is best to possess one area and set the information in it instead of three bit fields that has to be transalted. This is also true if you will have to make use of a situation statement with all of three fileds to obtain the information (say title) that you would like in confirming. Quite simply go over your characteristics and find out if they're truly separate or whether they can be abstracted right into a more general one.