field size quick question

G

garret

When creating fields, is it important to limit field size to as small
as possible for the reason of saving memory space? Or does the field
size have no effect on memory and once a record is created it takes up
X amount of space regardless?
 
D

Dirk Goldgar

garret said:
When creating fields, is it important to limit field size to as small
as possible for the reason of saving memory space? Or does the field
size have no effect on memory and once a record is created it takes up
X amount of space regardless?
Click to expand...

For text fields, the field size is just a limit on the number of
characters that can be stored, and has no effect (AFAIK) on the actual
amount of space the data occupies. Only the number of characters
actually stored in the field has any effect on the space occupied on
disk. I suppose it's possible that the field size may have an effect on
the amount of RAM allocated for the field when it's held in memory, but
if so, I don't think you need to bother about that for any practical
purpose.

It's different for number fields, where the different field sizes imply
a specific number of bytes used to store the data in this
representation.
 
P

peregenem

garret said:
When creating fields, is it important to limit field size to as small
as possible for the reason of saving memory space? Or does the field
size have no effect on memory and once a record is created it takes up
X amount of space regardless?
Click to expand...

Get the design correct. 'Logical' considerations are more important
than the 'physical'. To use an example, a US Social Security Number
(SSN) column can only be CHAR(9), rather than NVARCHAR(50) or even
VARCHAR(9), and needs additional CHECK constraints for the basic
regular expression pattern, which would test for the correct length in
the process.

For variable length text you may need to do some research. In my
country, Government data standards specify person_family_name as having
a maximum of 35 characters, so I use VARCHAR(35). Using VARCHAR(50) for
person_family_name may open my whole schema to ridicule e.g. can you
think of a person with a name anything close to 30 characters, let
alone 50?

You may not be able to get away from physical considerations, however.
In later version of Access/Jet, text columns are Unicode so for SSN
NCHAR(9) is the best we can do. That said, the abovementioned CHECK
constraints should ensure that Unicode characters will never actually
enter your SSN column.

If you have even the slightest concern about *storage* capacity, start
using a more capable DBMS than Jet immediately.
 
G

garret

I know this about using the Logical sense. I was just wondering if the
field size had any effect on memory size.
For example, I have a field called Description as a text field. Now,
no one will ever need all 50 characters that it supplies as a
default-but there is no real restriction either. However I have to
make several tables with this field, and although it doesn't take much
time to set the field size, I was wondering if it was even worth the
time to change these field sizes.
If it does change the memory size, then (for simple example purposes)
say it was 30 bytes now instead of 50.
After 5000 or so records, it starts to add up about how much memory is
wasted (20 bytes per record). I was just wondering if this was the
case or not...
 
P

peregenem

garret said:
I know this about using the Logical sense. I was just wondering if the
field size had any effect on memory size.
For example, I have a field called Description as a text field. Now,
no one will ever need all 50 characters that it supplies as a
default-but there is no real restriction either. However I have to
make several tables with this field, and although it doesn't take much
time to set the field size, I was wondering if it was even worth the
time to change these field sizes.
If it does change the memory size, then (for simple example purposes)
say it was 30 bytes now instead of 50.
After 5000 or so records, it starts to add up about how much memory is
wasted (20 bytes per record).
Click to expand...

For me, VARCHAR(50) for a 'description' sounds reasonable. Therefore, I
suggest that, rather than concentrating on sheer 'memory size', you
look at balancing the avoidance of database round trips (reads from
physical storage) against the need to keep 5000 records in memory. Or
are you talking in terms of storage on disk, rows per page, etc?
 
G

garret

Haha I'm getting a lot more out of this than I wanted to know.
But rather than memory (if you were thinking it as the term used to
describe how much the computer can handle at once) I mean storage on
disk space (or hard drive space). I think I really just want to know
this. Forget logical reasons, I'm just concerned about the space.
The reason I'm asking this is because my boss (who knows minimal access
knowledge) gave me a huge list of all the tables he wanted me to make,
including the names of each field in each table, and the field size. I
thought this was a waste of time since my thoughts were that a record
takes up space depending on what variable type it is, not what the
field size is, so I am questioning this belief with this thread.
 
N

Nikos Yannacopoulos

...my boss (who knows minimal access knowledge) gave me a huge list
of all the tables he wanted me to make, including the names of each
field in each table, and the field size.
Click to expand...

Hey, man, *this* is your real problem, not the amount of space! I got
$50 that says he'll be coming back asking this and that addition, so in
the interest of yor mental health you need to make sure the data
structure is well normalized right up front, so you don't get into dead
ends further down. This can be quite tricky if your boss's idea of the
data structure is both wrong and strong, which is why I said that this
is your real problem.

Nikos
 
G

garret

Nikos said:
Hey, man, *this* is your real problem, not the amount of space! I got
$50 that says he'll be coming back asking this and that addition, so in
the interest of yor mental health you need to make sure the data
structure is well normalized right up front, so you don't get into dead
ends further down. This can be quite tricky if your boss's idea of the
data structure is both wrong and strong, which is why I said that this
is your real problem.

Nikos
Click to expand...

Haha...this might be a problem, I don't think so though. I'll make it
work =). I was really just wondering about this one issue, it isn't a
problem or anything.
 
G

Garret

Also to add, another reason why I am asking this question is because of
abbreviation. For example, my boss wants to abbreviate all the names
of the machines, parts, etc. down to 6 characters long because he
thinks space will be saved. Personally I think that it would be much
easier to write out the full words so there is less confusion. If I
find out that restricting field space has no effect on hard drive space
or memory, then I will surely not set any restrictions and leave the 50
as default.
 
N

Nikos Yannacopoulos

Garret,

I have no idea what you are working on, but I would guess you would have
separate tables for machines, parts etc, with an ID field and a
description field (at least). Some simple math: assuming 10,000 parts,
the difference in storage space between 6 char. long description and 30
char.long ones is (30 - 6) x 10,000 = 240,000 bytes ~ 235KB... ouch,
this is big, will I still be able to fit my database in a floppy?

To confirm that increasing the size of text fields does not increase
file size if not fully populated, take an existing database, compact and
note down the file size; then change the field size of text fields in
the biggest table to 255, compact again and check file size: no change.

HTH,
Nikos
 
B

Brendan Reynolds

I will often store both a short abbreviation and a longer, more descriptive
name. The abbreviation takes less space on paper when printing tabular
reports, and that is often a much scarcer resource than hard disk space.
 
G

Garret

Garret,
I have no idea what you are working on, but I would guess you would have
separate tables for machines, parts etc, with an ID field and a
description field (at least). Some simple math: assuming 10,000 parts,
the difference in storage space between 6 char. long description and 30
char.long ones is (30 - 6) x 10,000 = 240,000 bytes ~ 235KB... ouch,
this is big, will I still be able to fit my database in a floppy?

To confirm that increasing the size of text fields does not increase
file size if not fully populated, take an existing database, compact and
note down the file size; then change the field size of text fields in
the biggest table to 255, compact again and check file size: no change.
Click to expand...

Thanks Nikos, this was the answer to the question I've been trying to
ask. I think you're right about my boss being the real problem though
=/. I'm not sure how much he knows about Access because the tables he
wants me to make seem very weird. Thats my problem though.
 
N

Nikos Yannacopoulos

I don't know how well this translates, but there's a greek saying that
goes something like: half-ignorance is worse than ignorance. You're
probably a victim of such a case. Sometimes it's harder to fight
preconceived ideas, than to fight the beast itself! Hang on there, and
post back for help if required.

Regards,
Nikos
 
G

Garret

Haha, I think I know what you mean. Anyway I appriciate your help
along with everyone else who posted.
Don't worry, I'll be sure to post back if I have any questions. Also
don't be surprised if you see any new topics started by me in near
future.
 
B

Brendan Reynolds

I believe the English equivalent would be: "A little knowledge is a
dangerous thing."
 
Top