Wrox Programmer Forums
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 16th, 2012, 07:12 AM
Authorized User
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default Complex query

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.

Last edited by masterlayouts; April 16th, 2012 at 07:15 AM..
Old April 16th, 2012, 10:23 AM
Rod Stephens's Avatar
Wrox Author
Join Date: Jan 2006
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts

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.)

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.
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 Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
complex query g_vamsi_krish SQL Language 3 February 27th, 2006 10:48 AM
query too complex liz@trinityholdings.co.za Access 3 August 28th, 2003 09:01 AM
COMPLEX QUERY PROBLEM nikosdra Classic ASP Databases 2 July 28th, 2003 02:13 PM
Complex Query-Plz Help rivinjose SQL Server 2000 10 June 27th, 2003 09:25 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.