Determine size of individual record & space taken in each table!

[zufie]

I have been tasked with the following:

See if you can figure out approximately how much space it takes to
store 1 individual (record)?

This would include all of the tables that contain the individual
(record).

How the heck do I figure this one out!?

Thanks,

John
(e-mail address removed)

 

[John Spencer]

This can be very tricky to do since it depends on the size of the data stored,
the types of fields, and how many records will fit in a block (and the size of
the block varies depending on the data engine), how many and which fields are
indexed, and whether or not compression is turned on or off.

Oh and it also is dependent of the data engine you are using, you are using
and the settings in the data engine and the settings for fields.

For the JET database engine (Access' "native" engine) and Access 2003.

Text fields are one (or two) bytes per character actually entered into the
field depending whether or not you have compression turned on. Plus some
overhead for Access to determine where the text starts and ends for each field.

Memo and OLE object fields use 8 bytes to address where the data is actually
located plus one (or two) bytes per character in a memo field. OLE Object
fields ???

DateTime fields = 8 bytes
Currency fields = 8 bytes
Yes/No field = 1 bit

Number fields (long) = 4 bytes
Autonumber = 4 bytes (or 16 if guid)
GUID field = 16 bytes
Number fields (double) = 8 bytes
Number fields (Integer) = 4 bytes?
Decimal numbers ?
Single Numbers = 2 bytes
Byte - 1 byte

And then you have to worry about indexes and how many bits are involved with
adding the record to any indexes.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I have been tasked with the following:

See if you can figure out approximately how much space it takes to
store 1 individual (record)?

This would include all of the tables that contain the individual
(record).

How the heck do I figure this one out!?

You probably can not get it precisely and it's near
irrelevant anyway. Records are limited to 2,000 bytes so
that's an upper limit. In general, each number type field
takes as many bytes as the size of the data type. For
example, Integer - 2, Long - 4, Double - 8, etc. Text
fields require 1 byte per character in the field's value
plus a few bytes of overhead. Memo fields are a small
number (8?) of bytes in the table and the memo's text is
saved somewhere else. Then there are the table's indexes
where each record requires some space.

Since a modern hard dirive can contain many GB of data and
an Access/Jet database is limited to 2GB, I think your task
giver is making a fuss about nothing.

 

[Jerry Whittle]

With Oracle and other industrial strength databases, there are easy ways to
figure this with views built for the job. Not in Access.

It possible to squeeze about 121,000,000 records into a 2GB mdb file. So
that's a minimum of about 16 bytes per record. Records which are useless.

On the other hand, a record can have a maximum of 2,000 characters so with
Unicode that makes 4,000 bytes max per record. That about a half million
records at max size.

But that doesn't include memo fields which can be 64,000k each with more
than one memo field per record. Actually I thing you can cram 1 mb into a
memo record using code. There's also other binary files such as Attachments
in Access 2007. I think that it would be possible to fill and Access database
with just one record.

If you are tracking Illinois governors, Access might not be the best tool as
our governors have rather extensive criminal records lately......