a_jam_sandwich Posted February 12, 2003 Posted February 12, 2003 Quicky I have a form with nearly 500 fields on it. a (FACT FIND DOC) At the moment i feel I have 2 opions. 1. Save all fields in to one massive table (Sortof Unmanagable) and use one update query 2. Have the fields split into 9 Tables from 9 pages and have 9 update querys Anyone with ideas would be appreciated Andy Quote Code today gone tomorrow!
Moderators Robby Posted February 12, 2003 Moderators Posted February 12, 2003 It depends, if there is some data repeated over and over such as Customer information and purchases they made. You would seperate the Customers, Products, Inventory, Sales etc... Can you give us some details on what type of data you have. Quote Visit...Bassic Software
a_jam_sandwich Posted February 12, 2003 Author Posted February 12, 2003 No one row per Client no info is duplicated Quote Code today gone tomorrow!
Heiko Posted February 12, 2003 Posted February 12, 2003 one table for all would make querying easier, i suppose. Quote .nerd
a_jam_sandwich Posted February 12, 2003 Author Posted February 12, 2003 i tend to agree from a point of view of how many commands but what is the performance hit for such sized tables? Quote Code today gone tomorrow!
Heiko Posted February 12, 2003 Posted February 12, 2003 Phew, me no DB tuner, sir. Now, from what I remember from years ago at uni, performance problems might (will) occur when you have many indexes (sp?) on the table and perform update/insert/delete statements. Also, MS SQL Server has a maximum row lenght! This might be the knock-out-criteria, because it is at approx 8KB (excuse my bad recollection, I am too lazy to look it up.) :-) Quote .nerd
Moderators Robby Posted February 12, 2003 Moderators Posted February 12, 2003 I understand you have one row per client, what kind of info is in there. Quote Visit...Bassic Software
a_jam_sandwich Posted February 12, 2003 Author Posted February 12, 2003 Lots and lots of text & memo fields Quote Code today gone tomorrow!
Moderators Robby Posted February 12, 2003 Moderators Posted February 12, 2003 I know that you have lots and lots of fields. In your first post you already said there were nearly 500 fields. Since I can't see your project from 3000 miles away, I'll ask again "what kind of info is in there"? Not to sound rude but In both my posts I'm trying to understand your data. Quote Visit...Bassic Software
Heiko Posted February 12, 2003 Posted February 12, 2003 If I understood the sandwich correctly, then he/she is basically concerned about performance. And this should be independent of the "business contents". You said mostly text and memo fields - so you're hooked on an Access DB, right? As you can't put an index on a memo field (as I recall) - there might be just a handfull of indexes. This would allow one large table. HOWEVER the locking mechanisms of Access have a very bad reputation. Are you programming a multi-user application ? Then use as many tables as possible :-) Also are you totally sure that there will never be redundant information? I can hardly imagine a case with 500 Fields in the Table and NONE of them basing on a discrete set of values - and NONE of them being directly related to each other? Quote .nerd
a_jam_sandwich Posted February 12, 2003 Author Posted February 12, 2003 Yup it a fact finder for a mortgage company so each row is individual to that Client and 95% is entered text yes Access and single user Quote Code today gone tomorrow!
*Experts* Nerseus Posted February 12, 2003 *Experts* Posted February 12, 2003 If I might clarify on Robby's question, can you break out some data logically so that you can use multiple tables? Only you know the real answer, but here are some considerations: Assuming this is a loan application and you need to gather some info about the client (the buyers) you might have tables: Buyer (first, last, middle, DOB, Sex, income, etc.) BuyerAddress (multiple records per Buyer, usually last 3 addresses) BuyerJob (multiple records per Buyer, usually last 3 jobs) etc. etc. If you want to list some of the fields or types of fields you're gathering/storing, we could better offer up what things should look like. You almost definitely CAN'T use one table, as most databases have a limit on the number of columns or MAX data. You can set it when you create your database, but it's generally not a good idea to create massively large tables. You don't want columns named "City1", "City2", "City3", "City4", etc. - instead you'll want to create another table to store addresses. Is that what you were looking for? As far as how you get the data in the database, that's a different question with an equal number of choices depending on speed, transaction support, etc. -nerseus Quote "I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
a_jam_sandwich Posted February 13, 2003 Author Posted February 13, 2003 I know very well how to create relational data and it does do that already for the quoting system. This is just a fact file free text relating to the Clients Financial situations hence why it so big. I think i will go the route of having multiple tables all relating to the CLIENT ID test if for speed if it ok all the better Thank you all for your help Andy Quote Code today gone tomorrow!
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.