Which of these types fields will take up less space....

[Mike]

I'm building a Quotation database and have the following question:

My Primary Key in the Quote Table is
"Quote ID"
I will have numerous other tables linked to the Quote ID so what ever I put
in that field will be used in many records & tables.

I know that a GUID takes up 16 Bytes of space.

Q1: How many bytes would a Text Field with 8 Characters take up?
For instance 3 letters and 5 digits
Q2: What would be the most efficient field type?
Q3: Would either one slow the database more once I had a large number of
records?

The reason I ask is that 3 other people will be using replicated databases
to put their quotes into and I want to be able to sync them without
duplication of Quote ID errors. At the same time, I don't want to have so
much overhead that I have problems in the future.

Thanks

 

[John Vinson]

I'm building a Quotation database and have the following question:

My Primary Key in the Quote Table is
"Quote ID"
I will have numerous other tables linked to the Quote ID so what ever I put
in that field will be used in many records & tables.

I know that a GUID takes up 16 Bytes of space.

Q1: How many bytes would a Text Field with 8 Characters take up?
For instance 3 letters and 5 digits

16 bytes (if you're using Unicode).

Q2: What would be the most efficient field type?

It really makes very little difference IME. The index hashing
algorithm is quite efficient.

Q3: Would either one slow the database more once I had a large number of
records?

You might be able to detect a difference between GUID, Long Integer,
and 8- or 16-byte text if you have a REALLY good timing algorithm.
With proper indexing users probably will not notice. What do you
consider "large"? Tens of millions of records is getting large for
Access.

The reason I ask is that 3 other people will be using replicated databases
to put their quotes into and I want to be able to sync them without
duplication of Quote ID errors. At the same time, I don't want to have so
much overhead that I have problems in the future.

A GUID is safest then. You can "roll your own" by using (say) a
three-byte string to identify the user, and a programmatically
maintained Long Integer sequential counter for that user's ID; a
two-field Primary Key might be a mite slower than a one-field one, but
I would be queasy about using a composite "intelligent" key.

John W. Vinson[MVP]

 

[Mike]

John,
To be honest, I'm not sure how big it will get.
But here an example of how I think I will set it up.

I have a Quote Number which has general quote information that never changes.
Since my customer constantly changes their mind and we have to submit
revisions to quotes, I'd like to store the quotes by revision. That way I
always have a history.
In each quote revision, I will have a Bill of Material which could contain
on average 20 items.

So, If I do 5 quotes (inlcuding revisions) per day, that's 100 records Per
Person Per Day. With 4 people in my group, that's 400 records per day, 2000
per week, 100,000 per year.

Keep in mind that there are also other smaller tables that will use the
Quote and Quote Revision IDs as well so it could get a little bigger than
that.

Thanks

 

[John Vinson]

John,
To be honest, I'm not sure how big it will get.
But here an example of how I think I will set it up.

I have a Quote Number which has general quote information that never changes.
Since my customer constantly changes their mind and we have to submit
revisions to quotes, I'd like to store the quotes by revision. That way I
always have a history.

You should then certainly have the Primary Key consisting of two
fields - Quote Number and Revision. If these are meaningful numeric
fields, I'd suggest simply using two Long Integers; or if the Quote
Number is text, a suitable sized text field and a separate Long
Integer Revision number.

In each quote revision, I will have a Bill of Material which could contain
on average 20 items.

But surely these are in a related table? You're not going to reenter
or duplicate the entire BOM for every revision, are you? I'd think
you'ld need to change or add only in a (thin) linking table.

So, If I do 5 quotes (inlcuding revisions) per day, that's 100 records Per
Person Per Day. With 4 people in my group, that's 400 records per day, 2000
per week, 100,000 per year.

So in ten years you'll be up to a million records. Piece of cake; WELL
within Access' capabilities - and who knows what you'll be using in
2014!

Keep in mind that there are also other smaller tables that will use the
Quote and Quote Revision IDs as well so it could get a little bigger than
that.

Still not a problem, IMO.

John W. Vinson[MVP]

 

[Mike]

John,
As far as I understand, I would need to duplicate the BOM for each revision.
I don't really know how to have a BOM for the original, and then either have
items added to or deleted from the original. Then the next question is on
say Rev 10... Do I create that by applying all the changes from rev 1-9? Or
just apply Rev 10?

Hmmm.... this somehow sounds plausible but I have no idea how to execute it.

Any help would be appreciated.

Thanks.

Mike