General discussion

Locked

Estimating a required database size

By mgilmore ·
Hi,

How can I estimate the size of a required database?
I have the number of records (persons), tables and an average of each number of fields (as well as a break up of numeric vs text fields) in the tables.

Thanks,
Hilary

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Estimating a required database size

by Jay Eckles In reply to Estimating a required dat ...

If you know how many fields there are per record, you can find the maximum size of the database by adding the maximum length of each field type, then multiplying that number by the expected number of records.

You can find the expected size of the database by estimating the expected length of the data that will go into each field. For example, if one field is phone number, you might expect that it would be 12 bytes long (given 999-555-1212 format). For something variable like a name, takeyour best guess. I'd guess most last names are about 8 bytes, most first names 6 bytes or so. Add up the expected size of each field, then multiply by the number of records, and you've got your expected database size.

Good luck.

Jay
www.jayeckles.com

Collapse -

Estimating a required database size

by mgilmore In reply to Estimating a required dat ...

The question was auto-closed by TechRepublic

Collapse -

Estimating a required database size

by Bojidar Alexandrov In reply to Estimating a required dat ...

You can't estimate size of database without knowledge of how data is phisically stored i.e. phisical database format and any database options. I you specify for what database do you want this, may be can help.
Also generaly you have database and log size.

regards
Bojidar Alexandrov

Collapse -

Estimating a required database size

by mgilmore In reply to Estimating a required dat ...

The question was auto-closed by TechRepublic

Collapse -

Estimating a required database size

by donq In reply to Estimating a required dat ...

There are a number of considerations:

The STRUCTURE for your database (or Data Warehouse) + the DATA that will fill each Field in each Table + the defined Relationships + any pre-defined Queries + any pre-defined Forms + any pre-defined Reports +any pre-defined Macros + any pre-defined Modules, etc.

What you explained with NO Macros, Modules, Active Server Pages, and a minimum of Forms and Reports (based on queries) would likely be 20-25KB + data (run-time) OR 37-42MB + data (executable). The first answer would work for calculating data where memo fields aren't used (they actually save memory).

Collapse -

Estimating a required database size

by mgilmore In reply to Estimating a required dat ...

The question was auto-closed by TechRepublic

Collapse -

Estimating a required database size

by sl-campbell In reply to Estimating a required dat ...

This will be a long winded one in several parts, but what the heck:

Assuming you are just talking about the database itself - a database which stores just the records, and that being an MDB table. Every thing else, forms, reports, etc., is basically static, for the most part, and you know the size of these will always be the same.

The field sizes for text fields are of course 1 byte for each character. So you can multiply this by the maximium number of characters that the field is set to, or, if you wish, the average number of chars.

Number fields always use the following:
Doubles, currency, DateTime - 8 bytes total
Single - 4 bytes
Long integers - 4 bytes
Short integers - 2 bytes

Now you can take the fields in the table and calc. the max:
Field1 =Text- 50 char max. =50 bytes
Field2= Text- 25 char. = 25 bytes
Field3=Number-Double= 8 bytes
Total = 83 bytes
Number of records= 100

Size = 100 * 83 = 8300 bytes.

HOWEVER, this is misleading with an MDB, and whiletrying to calculate the actual amount of space a certain amount of records need, which have a certain amount of fields, may be possible to a certain degree, it becomes almost impossible with multiple tables with different number of fields, or , if it is just one table, when you need to add a field later, calculating the size based on a value from the number of bytes each record held before, plus the new field, is almost senseless. Why? Because with each record is stored more than just the data itself.

Collapse -

Estimating a required database size

by sl-campbell In reply to Estimating a required dat ...

If I have a table with one text field and 500 records and each record has 10 characters, then I may be able to calculate the total size of all records and come up with a figure - but it won't be 500 * 10 * 1 byte. It'll be more like 500 * 10char *2.2 bytes. And if I double the number of records THEN I could think 1000 * 10char * 2.2 bytes. BUT, if I add a second field, the same size with the same number of characters then it may calulate to 1000 * 20 char.* 1.5 bytes, and a third field may be 1000 * 30char * 1.4 bytes.

The next part that is misunderstood is the fact that an MDB works with BLOCKS or PAGES of data. This amounts to 2048 bytes, which means that 1 record with one field will increase the database size by 2048 bytes, and adding 50 records MORE to it will not increase it at all - until the 2048 byte block is used up, and then one more record would increase it by 4096 bytes. Add another field the same size with the same data as the first field and, no, the database doesn't double exactly in size because what I mentioned before.

The last factor is the fact the deleted records also occupy space - until the database is compacted. Therefore, if a customer uses a database with basically the same AVERAGE number of records for 2 years and has alot of data changes going on, (Deletions, temporary tables are filled with data and deleted, etc.), that customer who started out with a 5 Mb database as an average may end up with a 100 mb database - until it is compacted. Try this: Using an append query add 100,000 records and then check the db size, then delete all of the records, and check the db size, and then add the records again and check the size. It may go from 30Mb after adding to remaining at 30 Mb after deletion and then to almost 60Mb after adding again - until compacted.

Collapse -

Estimating a required database size

by sl-campbell In reply to Estimating a required dat ...

Experience, and knowing what your program does with the data, are the only two factors that can be used here. You could fill a test database using a bulk append query with a 100,000 records pretty fast, and then compact it and size what size it is. This would probably be the best way to determine the size.
But you also have to take into consideration what happens with the data, (how often is data deleted, changed over a period of time, which could be alot in a short time, temporary tables), and based on that, tell your customer that the database holds an average of 25Mb of data but will increase to 50 Mb at times, until it is compacted, (daily, weekly, monthly?). When using temporary tables it would help to use a front and back end database on a multi user system.

Collapse -

Estimating a required database size

by mgilmore In reply to Estimating a required dat ...

The question was auto-closed by TechRepublic

Back to Web Development Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums