database design - different possible data types

[Daniel]

Hi everyone,

I'm trying to normalise my database, but am running into a problem due
to the field's data type.

Here's a simple example:

tblCables:
FLD. NAME.........FLD. TYPE
cableID .............. autonumber
size ................... string or double
unitsSystem........ boolean

If unitsSystem is "American", then I need to store the size as a
string, but if unitsSystem is "metric", then I want to store the size
in square millimetres (a double field). What is the proper way to
design this database?

Thank you,
Daniel

 

[Guest]

I do not believe that you will ever use the size in any math such as 3 times
14 AWG or 5 times 600 CM. Therefore use a text field. I have not use square
millimetres but I do not think you would be using math either.

 

[Daniel]

Hi Karl,

For this particular case it probably doesn't matter much (although
sorting will work properly for a number field), but I have another case
where I will have to perform math. Should I just convert strings to
numbers using the string conversion tools?

Does anyone have any other thoughts on this?

thanks

 

[Jamie Collins]

size ................... string or double
unitsSystem........ boolean

If unitsSystem is "American", then I need to store the size as a
string, but if unitsSystem is "metric", then I want to store the size
in square millimetres (a double field).

The best way is to store imperial/American values and metric values
using the same measure, if at all possible. Storing inches and
millimetres in the same column invites problems, IMO. Get a copy of Joe
Celko's book, 'SQL Programming Style', which has a whole chapter on
scales and measurements.

Two different data types in the same column is an obvious blunder, I
would hope. The value should be numeric. The Double (DOUBLE FLOAT) type
is an inaccurate/inexact data type and is unsuitable for measurements.
Instead, use an accurate/exact type, such as DECIMAL.

You may require an additional column to indicate the measurement but
you may end up with problems if you go with an 'is American' Boolean,
similar to the issues some have encountered with an 'is male' flag for
a person's sex. I'd suggest encoding the possible values, even if there
are only (at present) two values.

Jamie.

--

 

[Jamie Collins]

I do not believe that you will ever use the size in any math such as 3 times
14 AWG or 5 times 600 CM. Therefore use a text field. I have not use square
millimetres but I do not think you would be using math either.

That's not a good test for determining a column's data type, IMO. I may
not envisage using temporal functions on a column that will simply
timestamp another for audit purposes but there still is no question
that the type should be DATETIME.

A better test would be to ask: from which domain do the values derive?
Make this column text and it is almost certain to contain the value 'I
HATE MY JOB!' sooner or later.

Jamie.

--

 

[Daniel]

That's not a good test for determining a column's data type, IMO. I may
not envisage using temporal functions on a column that will simply
timestamp another for audit purposes but there still is no question
that the type should be DATETIME.

A better test would be to ask: from which domain do the values derive?
Make this column text and it is almost certain to contain the value 'I
HATE MY JOB!' sooner or later.

Jamie.

--

Thanks for your comments. In this case, I'm still not sure how to
apply your advice in this particular situation. I can construct 'if'
statements or subqueries which would accomplish it, but I can't think
of a 'straight' sql approach that wouldn't bother Access (subqueries
cause the recordset to be read-only). Using an 'if' isn't a big
problem, but it's not a clean database solution.

The problem arises because the Imperial units are not 'linear':
(approximate equivalencies):
AWG mm2
....
2 35
1 50
2/0 70
3/0 95
4/0 120
300MCM 150
....

The solution that keeps suggesting itself to me is a lookup table which
has a lookup to units in one column, the metric units as the 'size',
and the 'real' units in another column (e.g. "300 MCM" corresponding to
AWG or "25" for mm2) , but the string versus decimal problem remains.
Another option would be to have a column/table name in the 'real' units
column ... but that would be a subquery and I've learned not to go
there with Access.

There are workarounds, but I wanted to try & improve the layout of my
database. If I end up doing a lot more database design then I'll
probably try & get my hands on a good database book as you suggest.

thanks again,
Daniel