December 22, 2007 at 4:15 pm
Greetings all SSC members! Let me start of by saying I am VERY new to db design (read: this is my first real attempt) AND this is my first time posting here at SCC. I'm a ColdFusion programmer so I have basic SQL skills but now I've been asked to create an entire db structure from scratch. The application will be an online mortgage application service. One of the requirements is to support multiple buyers on a single sale (i.e. a primary and secondary buyer). I've created all the necessary tables to house the buyers with a relationship table to relate one buyer to another (and another and another if necessary). Additionally, I have a separate table to house various contact methods (e.g. Email, Home Phone, Work Phone, etc.) and another table that holds a particular buyer's contact value (e.g. 'someemail@domain.com', '954-555-1212', '305-123-1234', etc.). As you can imagine this type of design is resulting in multiple records for a single buyer with only the contact method and contact value differing. I was able to incorporate the new PIVOT command to merge the data into a single row. This works perfectly for a single buyer; but when I link a secondary buyer... BAM! I'm stuck with multiple records again.
The preceding brief and haphazard explanation leads to the following 3 questions:
1) Is the design I've built the "right" way to go (see condensed design below)?
2) How do I PIVOT twice?
3) There are roughly 20 tables with strict PK to FK ref. integrity. How do I know which column(s) to create indexes on? (P.S. - I understand this question is somewhat vague and off topic;) )
Please see the attached doc for CREATE, INSERT and SELECT statements.
NOTE: The buyer2 columns have been commented out to show the desired output format
December 22, 2007 at 8:01 pm
Hmmm... I don't really have time to review everything you posted, but it sounds to me like pivot is the wrong way to do it (although I still use SQL 2000 for most day to day things so I wouldn't have considered pivot anyway).
You are using dynamic SQL because you're not aware of the contact types ahead of time. Is this always the case?
Oh, and whilst I think about it, did you post the indexing question elsewhere on this forum?? 🙂 If it was you (and the tables seem really familiar) read that for indexing...
Perhaps you should take a step back. Why do you need the data formatted in this way from the database layer? If you have code running on the server, which I presume is cf, then why not return several recordsets - the first contains the buyer info, the second contains the contact info for each buyer - and process them in the server-side code? This is MUCH easier, faster, scalable, maintainable, etc. You also won't need any ugly dynamic SQL (although at least you're not taking unvalidated user input in this case) 🙂
Sorry for not answering your exact question, but since you have some server side code and this is a presentation issue, I would suggest that you leave formatting and amalgamation of some records to the web server code. Hope that helps! 😀
December 22, 2007 at 8:05 pm
1) Is the design I've built the "right" way to go (see condensed design below)?
2) How do I PIVOT twice?
3) There are roughly 20 tables with strict PK to FK ref. integrity. How do I know which column(s) to create indexes on? (P.S. - I understand this question is somewhat vague and off topic )
1. In my humble opinion, it sounds like you've done pretty well. The only mistake I see is relating 1 party to another. Parties should only be related to each other by a common item... the sale (attempt). Which means that you're missing a table or two... perhaps SaleAttempt or Offer or somthing like that for one table. There, you would have a new id number to represent the PK of the new table and a column with some common detail like whether the sale is pending, active, dead, or consumated as well as the property ID. A second new table would relate the buyers (one or more) as individual rows for each buyer to the sale.
2. Don't... it's not worth it. If you think you have problems with 2 parties, just wait until you run into 3, 4, or 5 (or more). There's nothing wrong with grouping related buyers vertically.
3. Pk's are inherently indexes. FK columns also make good candidates for indexes because of required joins to the parent table. That will likely take care of all of your indexing needs with the possible exception of some addtional criteria for reporting and some lookups... when you identify those, you'll know what additional columns to index. Probably not before then. Also, you may want to consider composite indexes but, again, you won't really know until you're designed some of the requirements for the Queries you intend to use.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2007 at 2:22 pm
@ian and @jeff - EXCELLENT detective work! Indeed both posts are mine. I figured it was valid since one was regarding design and the other about indexing. Thank you both for the suggestions.
@ian - I am in fact using CF for presentation so I see your point for performing the grouping in CF. I was attempting to do it in the db because I assumed it would be more efficient. And I'll check out the link you provided for indexing advice.
@jeff - I think I will heed your advice about doing away with the PIVOT and simply return multiple rows letting ColdFusion handle the grouping.
Thanks to both of you for the quick responses!
P.S. - This site and forum ROCKS!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply