Best approach for this problem?

[Joe]

I have to create a Microsoft Access 97 database (with a SQL Server back-end)
which acts as a quotation engine for an insurance company. The problem I
have is that each quote potentially has up to 300 unique pieces of
information stored in it.

What is the best way to structure the data for this database? As far as I'm
aware Access can't handle internal tables with more than 255 fields in them.

Should I store each piece of information "vertically" as a new row in a
table (rather than "horizontally" as a field)? The downside I can see of
this approach is that we could have up to 1,000,000 policies, and if each
policy has, say, 200 rows of information dedicated to it then we would have
tables with 200,000,000 rows in it (which I would imagine would run
slowly?).

I have a lot of experience of writing Access databases, but normally they
involve tables with less than 100 fields and Access handles them easily.

Please don't tell me I should read the basics of relational databases
because I already have. I'm looking for recommendations as to the best
general approach to tackle this problem.

Thanks in advance.

 

[Klatuu]

Joe,

If you are going to have up to 1M policies each with up to 300 pieces of
information, I am wondering if you are in danger of hitting an upper limit on
database size. I don't remember when Access went from a max of 1G to 2G, but
I would make sure that AC97 has the 2G limit before I proceed. I am not
trying to tell you what you should do, only passing some thoughts. Now,
assuming database size limit is not a problem, here is one possible solution.

I noticed you said "up to 300" and later said 200. This leads me to believe
that it will not always be the same pieces of information. Therefore, I
would suggest that you analyze the data requirements and see if you can come
up with some logical groupings of data elements where If you used one piece
from the group, then the probability would be high that you would use other
elements from the group. Then, build separate tables for each of the groups.
Of course, you would have to have a primary table that would carry common
data that would be used in all qoutes. Then do 1 to 1 relations from the
primary table to the other data group tables. I would try to keep the groups
as small as is reasonable to avoid carrying around any more empty space than
needed.

 

[Immanuel Sibero]

Hi Joe,

Please don't tell me I should read the basics of relational databases
because I already have. I'm looking for recommendations as to the best
general approach to tackle this problem.

OK, we'll take your word for it.

But one quick question, you described having to create an Access database
and the fact that it may be limited because of the size of your data. But
you also mentioned SQL Server as the back end. Where are you planning to
create the tables? Access or SQL Server? Both?


Immanuel Sibero

 

[Joe]

Hi Joe,



OK, we'll take your word for it.

But one quick question, you described having to create an Access database
and the fact that it may be limited because of the size of your data. But
you also mentioned SQL Server as the back end. Where are you planning to
create the tables? Access or SQL Server? Both?

When I've created quotation engines before in Access I've created the main
Quote form which has a local Access table as the data source for the form.
When a user loads a Quote, they transfer data from a back-end SQL Server to
the local Quote table. When they are finished editing the quote and want to
save it, they update the remote SQL Server Quotes table with the contents of
the local Quote table. So I will be using both SQL Server and Access tables.

BTW this is the method I've always used. Is there a better method for
loading and saving data?

 

[Joe]

Joe,

If you are going to have up to 1M policies each with up to 300 pieces of
information, I am wondering if you are in danger of hitting an upper limit
on
database size. I don't remember when Access went from a max of 1G to 2G,
but
I would make sure that AC97 has the 2G limit before I proceed. I am not
trying to tell you what you should do, only passing some thoughts. Now,
assuming database size limit is not a problem, here is one possible
solution.

Size isn't a problem because we are using a SQL Server back-end.

I noticed you said "up to 300" and later said 200. This leads me to
believe
that it will not always be the same pieces of information.

Not every piece of information will be needed for every quote.

Therefore, I
would suggest that you analyze the data requirements and see if you can
come
up with some logical groupings of data elements where If you used one
piece
from the group, then the probability would be high that you would use
other
elements from the group.

Yes, there are a few obvious groupings.

Then, build separate tables for each of the groups.
Of course, you would have to have a primary table that would carry common
data that would be used in all qoutes. Then do 1 to 1 relations from the
primary table to the other data group tables. I would try to keep the
groups
as small as is reasonable to avoid carrying around any more empty space
than
needed.

Yeah good suggestion, thanks. I'll probably end up doing this.

Now, what's the best way of developing the forms for this? I presume I will
not be able to efficiently load all this data into an Access form at the one
time, so I will need a bunch of separate forms which will pop up to allow
the user to add information belonging to different "logical groupings"?

 

[jl5000]

How about grouping your fields that have similar information, like one table
for personal info, another one for vehicle info, house info, etc.

 

[Paul Overway]

I would do the following:

1) Normalize any data that you can...and after you've done this...
2) For remaining fields, try to group fields logically into separate tables
to the extent it makes sense to do so
3) Create 1 to 1 relationships between the various tables from step 2

For item 2, I'm not really sure what this would entail without more
specifics, but I'm imagining that you might have policy related fields in
one table, customer in another, vehicle in another, etc.. However, the some
stuff I've imagined (customer, vehicle) are obviously not unique and
therefore should be normalized, i.e., a customer might request more than one
quotation (or at least you should hope they do), and they might have more
than one vehicle, etc.. So, these are probably bad examples of what could
get logically grouped in separated tables in a 1 to 1 relationship.
However, there may be other data that can be grouped somewhat logically.

 

[Immanuel Sibero]

Joe,

Seems like this thread has two issues: - table design strategy (your
original post) and -data storage mechanism.
I'll let the others chime in on your original post. I'm commenting on data
storage mechanism:

When I've created quotation engines before in Access I've created the main
Quote form which has a local Access table as the data source for the form.
When a user loads a Quote, they transfer data from a back-end SQL Server to
the local Quote table. When they are finished editing the quote and want to
save it, they update the remote SQL Server Quotes table with the contents of
the local Quote table. So I will be using both SQL Server and Access

tables.

At first, I was thinking that you are merely retrieving one record of policy
at a time from the SQL server, load this record to the local Access table
and then make changes on the form and save it back to the SQL Server. At
this point, I'm already thinking that this is not the best way to do it, but
then I realize that what you're doing may even be worse. From your earlier
post you mention capacity issue of Access, this makes me think that you're
actually loading your local Access table with the whole records from the SQL
Server?? Are you duplicating the whole table?? As with any duplication,
you'll have synchronization issue. What if you have two or more users?? How
do you ensure that each user's local table is updated with other users'
changes??

Or have I misunderstood you??

BTW this is the method I've always used. Is there a better method for
loading and saving data?

Yes. Loading and saving directly to the SQL Server!!
You can use bound form (just like the one you're currently using with your
local Access table) or unbound form directly against the SQL Server table.
If you want to simulate what you're doing now, you can use unbound form.

Again, apologies if I have misunderstood.



Immanuel Sibero

 

[Norman Yuan]

Yes, I was also wondering exactly the same as you did, regarding the OP.

If he uses SQL Server as backend and Access as front end, why he has to
think column numbers and *.mdb file size?. Access is merely used as a
convenient development environmet and user interface, while the table
already properly structured on the server. He can link to the server and
manipulate data, no need to for a user to grab hundreds of thousands records
to the front end, why is the file size concern? As for column number, why he
has to grab all 300+ columns to the front end the same time. In that way, he
needs to page them for several screens to just show them, while he can
easily organise them into server pages and just grab data for the page user
want to view/process (user may never need to view other pages)... Anyway, it
is just not that clear what information the OP conveys.

 

[Joe]

Joe,

Seems like this thread has two issues: - table design strategy (your
original post) and -data storage mechanism.
I'll let the others chime in on your original post. I'm commenting on data
storage mechanism:

Plus I would add a third issue - displaying this data in forms. I normally
do a tabbed pane for quotation forms i.e. all data is loaded at the one
time. I don't think this will be a good idea for 200+ pieces of information.

tables.

At first, I was thinking that you are merely retrieving one record of
policy
at a time from the SQL server, load this record to the local Access table
and then make changes on the form and save it back to the SQL Server.

This is correct. One record at a time to the local Access table, modify the
data in it then save it back to SQL server back-end table.

At
this point, I'm already thinking that this is not the best way to do it,
but
then I realize that what you're doing may even be worse. From your earlier
post you mention capacity issue of Access, this makes me think that you're
actually loading your local Access table with the whole records from the
SQL
Server?? Are you duplicating the whole table?? As with any duplication,
you'll have synchronization issue. What if you have two or more users??
How
do you ensure that each user's local table is updated with other users'
changes??

Or have I misunderstood you??
Yes.


Yes. Loading and saving directly to the SQL Server!!
You can use bound form (just like the one you're currently using with your
local Access table) or unbound form directly against the SQL Server table.
If you want to simulate what you're doing now, you can use unbound form.

Yes I understand that I can bound the SQL Server table to the Access form.
The problem I have with that is it writes directly to the table every time
the user updates something in the form... or does it (or does it wait for
me.refresh to write to the table)? With my current method of copying one
single record from the SQL table to the local Access table I can ensure that
changes can be easily undone, any writing is done entirely locally until the
user is sure they want to save it. Does binding the SQL table to my Quote
form ensure this?

Again, apologies if I have misunderstood.

NP, and thanks for the answer.

 

[Joe]

Yes, I was also wondering exactly the same as you did, regarding the OP.

If he uses SQL Server as backend and Access as front end, why he has to
think column numbers and *.mdb file size?. Access is merely used as a
convenient development environmet and user interface, while the table
already properly structured on the server. He can link to the server and
manipulate data, no need to for a user to grab hundreds of thousands
records
to the front end, why is the file size concern?

File size isn't an issue because I only transfer single rows to the local
Access table.

 

[Joe]

I would do the following:

1) Normalize any data that you can...and after you've done this...
2) For remaining fields, try to group fields logically into separate
tables to the extent it makes sense to do so
3) Create 1 to 1 relationships between the various tables from step 2

For item 2, I'm not really sure what this would entail without more
specifics, but I'm imagining that you might have policy related fields in
one table, customer in another, vehicle in another, etc.. However, the
some stuff I've imagined (customer, vehicle) are obviously not unique and
therefore should be normalized, i.e., a customer might request more than
one quotation (or at least you should hope they do), and they might have
more than one vehicle, etc.. So, these are probably bad examples of what
could get logically grouped in separated tables in a 1 to 1 relationship.
However, there may be other data that can be grouped somewhat logically.

There is some obvious groups. Thanks.