I have presented "education" and "experience" as linked to a table "user", but in reality it is a little bit more complex. I have "users" that may contain both "employees" and "employers", than I have tables related to each of them separate, like (I slim down columns for brevity):

I used an artificial key as id, even if the email should be unique. It is preferable this way because I can pass the id as parameter in scripts, so it make things easy. The id is also similar to a SKU for a product, so I guess it is not such a big mistake in this situation.

Does it make any sense to have an userID and an employeeID and employerID? Or even eliminate altogether the userID? I believe I'm fine here...

Contact information, as in your book, are taken out in separate table as a company or an employee may have several contact phone numbers (faxes...etc.). I guess the addresses should have been taken out as one company may have several offices, maybe in the future.

I guess it make sense until now, where I have some doubts is related to assembling together one's CV.

I have things like "experience" and "education" and "driving license", also "personal details" and several others tables for a total of 7 tables. All these should form a regular CV that should be displayed as a whole when an employer is looking to an employee. Now adding to the complexity there are the mentioned "contact/phones" and (eventually) "addresses", another 2 tables. So the total amount of queries will be high as tables like phones, addresses, but also experience, education...etc. have multiple rows of data for one user.

I have some concerns related to performance because there are so many tables and so many queries. Unfortunately this is one person's CV and displaying all data require to collect the data from all tables related to a particular id... it looks to me there is no way around. I would kindly ask you for your best advise.

For big tables like this, people usually use an employee ID. You kind of need one because two employees could have the same name.

If you assign the email address (i.e. it's a company email address instead of an external email address) then you might be able to use that as the primary key, but if it's an external email address then the employee might change it and primary keys don't usually allow changes.

So I'd use an EmployeeId here. (And UserId, CustomerId, CompanyId, etc.)

If you're just fetching the data for one person, this should be no problem. The only time you may have performance trouble is if you need to compile a list of thousands of CVs all at once. Then you may need to run the report (or whatever it is) over night.

The obvious alternative is to denormalize and combine fields in one record. For example, put a list of skills in a single employee field. That's usually a pretty bad mistake. It makes it much harder to validate the data and makes searching for values that have been combined extremely hard.

Another possibility would be to create a read-only datamart-style merged database that combined the desired fields. Then if you need thousands of records, you can get them from the merged database. Whenever you create or edit a record, you would need to update the merged database.

The cost is the extra time you spend whenever you create or update a record and the extra storage space. The benefit is you can quickly get the combined data.

I would try doing without the merged database and just perform the big join and see if it is a problem. It will be easier later to add the merged database if needed rather than removing it if it turns out not to be needed.

__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)