I have a program that has a lot of tabled data, should I have one DB for each save?

Question

I am making a construction bid program. Each bid will have 2-6 tables of data. Some of the tables (such as 'materials') might be quite large.

Do I save each bid as a separate database with 2-6 tables?
Or should I just do something more like comma delimited text files?

The number of tables will always be the same. I just haven't decided on how many I need yet.

Answer 1

Use the power of a relational database....
Relate information from three tables

I would make a single materials table which would be referenced by all bids. You can come up with a partnumbering system which would be easy to use and remeber which will also serve as the unique key for each material record.
i.e plywd38 for 3/8 inch plywood

Next is a bid table which contains a unique job number as a key index

finally a third table MaterialList this table references a job number from the bid table and a sequential item number. This multi field key is used to record a part number from the materials table and a quantity. (you may add other field such as a remark)

These three tables are a minimum required to relate a given material list record to a partnumber and bid number.

You could add a 4th table Customer which would relate customer info to the bid table

Answer 2

I talked to a bunch of people in regards to a similar problem a year ago, I was writting a similar program that required multiple tables per customer.

What I realized is very simple:

$sql = SELECT * FROM CUSTOMER001_MATERIALS

doesn't seem bad, until you get 100+ customer tables

$sql = SELECT * FROM MATERIALS WHERE CUSTOMERID = 100;

This would only display the records from customer 100, which is the same as the command above, but instead of 1 material table per customer, it is just 1 material table.

any database server can return the correct fields so quickly it really is better to stick them all into the same tables, so all customers are in the 4 tables, not 4 tables per customer.

If you need to add a column you are only adding it to 1 table, not 100 tables. Just imagine if down the road you decide to add a sales tax column to your materials table, it would be hundreds of hours of work handing adding it into a hundred tables, or a matter of seconds to add it to a single table.

Answer 3

you will probably do whatevr you want, why ask me?

make the tables and sell them i guess