Dates - dumb question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello -

I am trying to normalize a table that has a date field in it. There are
going to be 300 users per day that handle about 60 calls per day. Normally,
I would have a date field in the main table and have a default value of
"Date()" to automatically put a date in. I started thinking that this table
will consist of 300 multiplied by 60, or 1800 dates that are the same for
each day of the year! How is this normally handled? Would I create a
separate table just for date?

Another question - when you create a lookup field, isn't that adding extra
text to your table that makes it larger and actually not normalized?
 
Sandy said:
I am trying to normalize a table that has a date field in it. There are
going to be 300 users per day that handle about 60 calls per day. Normally,
I would have a date field in the main table and have a default value of
"Date()" to automatically put a date in. I started thinking that this table
will consist of 300 multiplied by 60, or 1800 dates that are the same for
each day of the year! How is this normally handled? Would I create a
separate table just for date?

Another question - when you create a lookup field, isn't that adding extra
text to your table that makes it larger and actually not normalized?
Click to expand...


I doubt that there is anything to be gained by using a
foreign key to get to the date in a separate table. Since a
date value is stored as a double number, you could save a
few bytes, but at the overhead of extra joins in your
queries. OTOH, you may want to consider whether you have
the date field in the right table. Do you have an entity
that is specific to each day?

Lookup ***fields*** in a table are a complete waste. Since
you should never let users edit tables directly, but only
through one of your forms, they serve no good purpose and
obscure the fact that what you see is not what you get. The
whole arrangement is properly normalized since the value of
the lookup field is a foreign key to the lookup table.
However, you as a developer should want to see the real
value instead of some descriptive text. Providing users
with a form using a combo box is certainly a preferred UI,
but it is in no way necessary for you to do that in a table.
 
Hi Marshall -

Thanks so much for your response. I wonder if I might ask your opinion on
the following:

tblMain - CallID, CallerName, NumbApps, Comment, CallTypeID, LoanTypeID,
AgentID, DateID, ResolvedID

tblCallType - CallTypeID, CallType (17 choices)
tblLoanType - LoanTypeID, LoanType (only 2 choices, business or home)
tblAgent - AgentID, AgentName (300 names)
[tblDate - DateID, CallDate (which I'll probably go with your suggestion and
make it part of tblMain)]
tblResolved - ResolvedID, Resolved (this is Yes or No)

In your expert opinion, did I set this up right? Is there a better entity
to put Date under that tblMain?

Any help is greatly appreciated!
 
Sorry, but if you want to know the day that the call was documented,
you'll have to have a field for it. Otherwise there will be no way to
differentiate when a call was documented.

As to the second question, I do not believe that the value from the
lookup table is stored in the table, just the value used to get it. In
other words if you do a lookup on LTC and it returns Lincoln Town Car,
only LTC is stored. The lookup is just for appearances.
I however do not have extensive experience with using lookup fields so I
might be incorrect.
 
Sandy said:
Thanks so much for your response. I wonder if I might ask your opinion on
the following:

tblMain - CallID, CallerName, NumbApps, Comment, CallTypeID, LoanTypeID,
AgentID, DateID, ResolvedID

tblCallType - CallTypeID, CallType (17 choices)
tblLoanType - LoanTypeID, LoanType (only 2 choices, business or home)
tblAgent - AgentID, AgentName (300 names)
[tblDate - DateID, CallDate (which I'll probably go with your suggestion and
make it part of tblMain)]
tblResolved - ResolvedID, Resolved (this is Yes or No)

In your expert opinion, did I set this up right? Is there a better entity
to put Date under that tblMain?
Click to expand...


I think table main is modeling the call entity, in which
case I agree. Since the date of a call is an attribute of
of the call, the date defintely belongs here. The only
change I would make is to change the name of the table from
Main to Calls, but this is technically unimportant beyond
clarifying what the table represents.

I would expect tblAgent to have more fields (attributes) for
the Agent entity, e.g. address, phone, etc.

I think tblDate is overkill since there is no such thing as
a date entity.

I don't understand what entity tblResolved is modeling. It
seems that resolved may be an attribute of the Call entity.
 
Hi David -

Thanks for responding.
Sorry, but if you want to know the day that the call was documented,
you'll have to have a field for it. Otherwise there will be no way to
differentiate when a call was documented.
Click to expand...

Would you just be able to do a join with tblAgent and tblMain to get the
date that's in the tblAgent table?

Let me know if I am thinking correctly about this.
 
Haven't been following the conversation about the specific tables.
Having a field for the record to capture the call date is perfectly
acceptible.
 
Marshall -

You're right. I think I'm over-thinking the issue. Thanks for all of your
input!
 
Sandy said:
You're right. I think I'm over-thinking the issue. Thanks for all of your
input!
Click to expand...

You may be over-thinking this, but that's alright.
Over-thinking is way better than under-thinking ;-)

Maybe you can recognize the point where you don't need
another table is when there will never be a need for the
table to have more than a single data column. Not a hard
and fast rule, but possibly a useful guideline.
 
I would reiterate that. The intent behind what you were asking about is
a *good* thing. I've seen multiple times where people have gone to the
other extreme where they've had problems understanding the nature and
use of a database (ex 130-fields for 1 table)
 
Back
Top