[thelist] SQL help - I think JOINing is required

Andrew Forsberg andrew at thepander.co.nz
Mon Nov 12 14:24:51 CST 2001

>a lot of database design is like that -- eminently sensible, even if some
>people like to obfuscate it with weird rules that remind you of math class

that's what i figured. :) still, they will come in handy when a 
design is very complex.

>i would consolidate those tables regardless of future relationships with
>other tables
>the redundancy that is removed by consolidating 1-1 tables is basically
>just the foreign keys


At 11:03 PM -0400 11/11/01, Marc Seyon wrote:
>General Info - Name, etc and a unique ID,
>Contact, Job, Education.

contact, job, education are all a bit of the unknown in this case. 
i'm guessing there's something like this going on:

Contact - contact1 VARCHAR, contact2 VARCHAR, contact3 VARCHAR, etc etc etc.
Job - job VARCHAR, start_date TIMESTAMP, end_date TIMESTAMP
Education - formal TEXT, in_house TEXT, qualifications TEXT

sorry, that's really rough; my point is that there's the potential 
for a large number of TEXT (or BLOB, i suppose) columns. i can see 
why you would consider merging those three tables into one (say, 
StaffDetail, renaming General Info: Staff), with a Staff_ID foreign 
key. now, say another table comes along like this:

Customer_Feedback - ID, staff_ID, customer_no, date_logged, feedback

grouping customer feedback by the staff's ID, and selecting the staff 
member's name along with the feedback probably wouldn't take that 
much longer if all of the staff info was in one table. but i would 
argue it's not as clean as it would be if there was a staff table 
with the bare minimum of columns (e.g.: id, name, boss_id), a table 
with all the fluff, and a third table with, say, customer feedback.

>but as i suggested yesterday, you gain simplicity (especially in the query)
>even though you sacrifice infinitesimally on performance -- for example, if
>you only need general info columns for a particular query, the database
>still has to retrieve full rows off the disk in order to extract the
>general info columns for you, so it's a bit less efficient, but probably
>only  by microseconds, so it doesn't matter unless there are millions of

i wonder how large TEXT / BLOB columns would need to get before they 
made an impact?

>  > How much emphasis would you put on separating out data into
>  > different tables when it's not strictly-speaking dependant on the key?
>a lot
>the most important part of database design is understanding the keys

i think that's why i'm questioning the 'stick all that in the same 
table' suggestion. other tables will heavily rely on Staff.ID, 
Staff.last_name, Staff.first_name; it's a piece of cake to grab extra 
staff information, if necessary, from a StaffDetail table, for 
example. why clutter up the table which is most likely to be used in 
every single query to the db?


Andrew Forsberg
uberNET - http://uber.net.nz/
the pander - http://thepander.co.nz/

More information about the thelist mailing list