Database Table Size Calculations

As a professional Developer, every once in a while you'll need to create a Database, for one reason or another. It's always nice to apply structured thought to your design process and work out your proposed database size before who release it upon the unsuspecting database server.

It's kinda obvious to do, but just in case heres how i do it.

First create your data dictionary. In my case it was for a questionnaire management system, (QMS, might release it as OSS when i'm done).

i wanted to dynamically generate a new table for each questionnaire, so i need to work out the system limits before hand.

first we would have our "user id's"

id - UInt
hostName - 128 byte (unicode 64 character string)
ip addr = 15 bytes (it's ip v6)
assocQuestionnaire = 4 bytes aka 32bit unsigned integer

then depending on the questionnaire you could have upto 32 questions/ comment, (32 columns). These could then store upto 1024 characters so thats basically double in UTF-8, so 2048 bytes, bringing in each row at almost 65Kb, now the company i work for has 40 000 members of staff, so lets assume they all anwser the same questionnaire (Great advertising for me), the database would swell to 2.5 Gb!

Well i think thats sight over kill, so by tweaking the the system to only implement the larger fields when requested by a user, and assuming that they only want one comment field per questionnaire, we get a much more wholesome figure of 100 Mb.

So to summerize the formula is

sum( column types ) * num of rows.

Remember this is only an estimate, and doesn't account for the master tables or relationships or any of that other RDMS goodness. So it's a guide not, E=MC2

Jonathan