Updating tables

[a_jam_sandwich]

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

 

[Robby]

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.

 

[a_jam_sandwich]

No one row per Client no info is duplicated

 

[Heiko]

one table for all would make querying easier, i suppose.

 

[a_jam_sandwich]

i tend to agree from a point of view of how many commands but what is the performance hit for such sized tables?

 

[Heiko]

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.)

 

[Robby]

I understand you have one row per client, what kind of info is in there.

 

[a_jam_sandwich]

Lots and lots of text & memo fields

 

[Robby]

I know that you have lots and lots of fields. In your first post you already said there were nearly 500 fields.

Since I cant see your project from 3000 miles away, Ill ask again "what kind of info is in there"?

Not to sound rude but In both my posts Im trying to understand your data.

 

[Heiko]

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 youre hooked on an Access DB, right? As you cant 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?

 

[a_jam_sandwich]

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

 

[Nerseus]

If I might clarify on Robbys 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 youre gathering/storing, we could better offer up what things should look like.

You almost definitely CANT 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 its generally not a good idea to create massively large tables. You dont want columns named "City1", "City2", "City3", "City4", etc. - instead youll 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, thats a different question with an equal number of choices depending on speed, transaction support, etc.

-nerseus

 

[a_jam_sandwich]

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