[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
hmmmm:
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
>records...
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?
cheers
andrew
--
Andrew Forsberg
---
uberNET - http://uber.net.nz/
the pander - http://thepander.co.nz/
More information about the thelist
mailing list