Design suggestions for storing fields in a contract

Question

Design suggestions for storing fields in a contract

los

Right there with Babe

Points: 782
More actions
May 10, 2008 at 12:47 pm

#190410

Greeting to all. I've sort of been forced into the position of acting DBA and my current project has me looking for the best design to store various real estate contracts, all the fields that can be filled in on those contracts and the values the user enters via a web form.
For instance, there will be approximately 200 contracts. Each one will have anywhere from 20 - 100 fields that need to be populated. Furthermore, some of the fields need to be pre-populated with defaults. The user will be presented with a web form built using ColdFusion that will allow them to enter data for each field.
To summarize, I need to store the names of the contracts, the fields that make up the contract (which will vary from contract to contract), the default values for some of the fields, and finally the values entered by the user for each field.
I had a couple desing ideas in mind but I wasn't really satisfied with either. One consisted of 7 tables to store the necessary info. The other was storing the fields and values as XML.
ANY suggestions offered will be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Without knowing what the 7 tables are, I'd say that's the better choice than trying to store it all as XML both from a design/maintainability aspect and a performance aspect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

cs_troyk SSCertifiable Points: 5383 More actions · Answer 2

I know people don't like to hear this, but the best suggestion I would offer is to engage the services of a data management professional.

Unless, of course, this is a "toy" project or one that is not sufficiently funded by the company you work for (which amounts to the same thing). If that's the case, you really won't go wrong following any design you may get from the newsgroups absent the myriad unstated requirements in your request.

HTH,

TroyK

los Right there with Babe Points: 782 More actions · Answer 3

Thank you for the replies...

@jeff

In case you're interested, I have attached the tables schema. For what it's worth, the only reason I was considering going the XML route is because the data will be static (for the most part) and I would only be requesting a single record at a time.

@cs_troyk

This will be much more than a "toy" project. Unfortunately, the budget for this is exactly $0.00! 🙁

By posting here, I was hoping someone may have encountered a similar situation and would share his/her method of implementing a solution. I'd be more than happy to provide additional detail if that would help.