Best Practice for tables?

[Kamitsukenu]

Hi there,

The company I work for mail items dependent on destination, weight and speed
of delivery.

I'm trying to make a system in Access, and during setting up all the tables,
I'm getting myself more and more confused.

I have the following tables;

(tblCountry) - holds the Country names
(tblDispatchMethod) - the service the item is sent by
(tblSupplier) - who the item is mailed by

Now, as dispatch of items are dependent on the weight, I have another table
which combines all the above information.

TblRoutingSystem contains
'CountryName' which refers to 'tblCountry'
'DispatchMethod' which refers to 'tblDispatchMethod'
'NameofSupplier' which refers to 'tblSupplier'
'WeightBandStart' and 'WeightBandEnd' which refer to (surprise, surprise)
the start and end of the weight bands for deciding the correct supplier for
mailing.

Does this sound about right, or should I be split them out further or should
I be merging more stuff together.

in fact, can someone go through the pros and cons of creating more tables to
suit the data?

Thanks

K

 

[Arvin Meyer [MVP]]

It sounds right if it's that simple. Instead of using the actual text from
the 3 tables, use the ID or Primary Key value.The tblRoutingSystem should
contain:

CountryID
DispatchMethodID
SupplierID

Now if the Supplier decides to sell the company or change its name, you
simply change it once in the Supplier table, and it propogates throughout
the database from the queries used to display the SupplierName.

 

[Kamitsukenu]

Hi Arvin,

Thanks for clearing that one up for me Arvin. You're right though, it does
seem too simple!

Kind Regards,

John

 

[Steve]

FYI in case you are interested ---

A service I provide is to design the table structure of a database for a
customer. I have done this for numerous customers. My fee is very
reasonable. I provide a map of the tables that shows all the tables in the
database, all the fields in each table, all the relationships between the
tables and the type of relationship for each relationship. The tables are
arranged on the map generally as the flow of information in the database. I
create a map of the tables for every database I do. The map visually shows
what forms and subforms are needed for data entry, shows what special forms
and subforms can be created for dispaying data in the database and shows
what reports and subreports can be created from the data in the database.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Marshall, MVP]

These newsgroups are for FREE peer to peer support, not for you to sell your
snake oil. There was nothing in Kamitsukenu's post that indicated he was
interested. It was a simple thank you to Arvin.

Please take your solicitations and phony testimonials somewhere else.

John... Visio MVP

 

[Tony Toews [MVP]]

(tblCountry) - holds the Country names
(tblDispatchMethod) - the service the item is sent by
(tblSupplier) - who the item is mailed by

Don't bother with the tbl prefix. Basically it's a waste of time.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

My fee is very reasonable.

Please stop soliciting customers in the forums.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Steve]

QryCountry
QryDispatchMethod
QrySupplier

Would you also advise the OP to not bother with the Qry prefix?

Suppose he has Country, DispatchMethod and Supplier tables as well as
Country, DispatchMethod and Supplier queries. When he uses these as
recordsources for forms and reports or in code, how is he suppose to know if
the reference is to the table or query?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Tony Toews [MVP]]

QryCountry
QryDispatchMethod
QrySupplier

Would you also advise the OP to not bother with the Qry prefix?
Yes.

Suppose he has Country, DispatchMethod and Supplier tables as well as
Country, DispatchMethod and Supplier queries. When he uses these as
recordsources for forms and reports or in code, how is he suppose to know if
the reference is to the table or query?

My queries have much more descriptive names than one word. I
frequently run up against the 50 or 52 character limit.

So this is a non issue.

Also see Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Gina Whipp]

Tony,

Just my opinion...

I use the object naming and then take it a step further...

tblOrders
oOrderID-PK
oDate
oClientID
etc...

tblOrderDetail
odOrderID-FK
odItemID
odDescription
etc...

qryOrders may contain
oOrderID (from tblOrders)
cpCompanyName (I know this comes from tblClientProfile)
oDate (from tblOrders)
odItemID (from tblOrderDetails)
odDescription (from tblOrderDetails)

If I have to do a DLookUp, I know whether I went to the table or the query
to get my information. I think that best naming convention is the one that
works for you and you will follow thru on.

 

[Steve]

50 some character table names and query names and you are advising the OP to
drop 3 characters from his table names???? Not much logic to that.

You need the advise rather than the OP. Shorten your table names and query
names and save a lot of time typing and reduce the probability of typos.

Unbelievable, Tony!!!!!!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Arno R]

Unbelievable, Tony!!!!!!!

Too bad for you Steve that !!!!!!! is not allowed in the objectnames ....

Arno R

 

[John Spencer]

I respectfully disagree with Tony. I prefix my queries with a designator
(usually just the letter q, but sometimes qry).

I do this since I tend to use the query wizards to get me started when I am
constructing my queries and the leading character lets me know when the
interface is displaying a query or a table.

Personal preference. On the other hand, I agree with Tony - I don't see any
reason to prefix tables with TBL or fields with fld. That usually just
means more typing for no real benefit that I see. Again, if it works for
you - go ahead and use it.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Arvin Meyer [MVP]]

If I have to do a DLookUp, I know whether I went to the table or the query
to get my information. I think that best naming convention is the one
that works for you and you will follow thru on.

I agree with that. I use the Leszynski/Reddick naming convention, almost
exactly as it was published. I only do it because other programmers often
work on the same projects and I like to maintain some consistency with a
generally accepted method. If you are the only person working then whatever
consistency you use is really up to you.

I also keep my naming as short as possible, but still able to adequately
identify every object. I do have some very long names, but most are under 15
to 20 characters.

 

[Tony Toews [MVP]]

50 some character table names and query names and you are advising the OP to
drop 3 characters from his table names???? Not much logic to that.

Ah, one reason is that in the database container window you can just
hit the first character of a group of queries, forms, whatever. Then
you can quickly go to that set of objects. Such as P for PO or I for
inventory.

You need the advise rather than the OP. Shorten your table names and query
names and save a lot of time typing and reduce the probability of typos.

Huh? When I need a object name I click once on the object, wait a
second, click again as though I were going to rename the object and
copy the name. Done. No types.

Unbelievable, Tony!!!!!!!

Clearly you have not a clue.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I use the object naming and then take it a step further...

tblOrders
oOrderID-PK
oDate
oClientID
etc...

With the exception of the tbl I agree with you.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Gina Whipp]

Tony...

Just curious... (Always willing to learn something new!)

What about in the case of DLookUp's (just one example), how would you know
where you are pulling the data from? Or do you table and query names never
match?

 

[Tony Toews [MVP]]

Just curious... (Always willing to learn something new!)

What about in the case of DLookUp's (just one example), how would you know
where you are pulling the data from? Or do you table and query names never
match?

I never use DLookups but code my own using recordsets and queries
usually defined in VBA code.

My table and query names can never match as my query names are quite
lengthy and descriptive and always have spaces. (Well almost always).
For example in a simple system I built for memberships the table names
are

Activity
Emails
GlobalOptions
Member
MemberFamily
MembershipActivity
MembershipEmails
Status

And the query names are

Acknowledgements to be sent via email
Acknowledgements to be sent via snail mail
Action - Acknowledgements
Action - Renewals
Active members
Activity - Renewal
Activity Summary - MembershipActivityDetails
AGM Notices to be sent
AGM Notices to be sent - Actiivity Append
Integrity checker - Renewals but not active
Membership Acknowledgements - Outstanding
Membership Activity History
Membership Renewals - Outstanding
Membership Renewals Outstanding
MembershipDetailReport
MembershipNoneDetailReport
Name Badges to be sent via snail mail
Non members with Email Addresses
Renewal Acknowledgements to be sent via email
Renewal Acknowledgements to be sent via snail mail for Pending R
Renewal Notices to be sent
Renewal Notices to be sent via email
Renewal Notices to be sent via snail mail

The code I used to produce the above is (as adapted from A97 help)

Sub ListQueryNames()

Dim qry As QueryDef, db As Database

Dim dbs As Database
Dim qdf As QueryDef

Set dbs = CurrentDb

For Each qdf In dbs.QueryDefs
Debug.Print " " & qdf.Name
Next qdf

End Sub

Sub ListTableNames()

Dim qry As TableDef, db As Database

Dim dbs As Database
Dim tdf As TableDef

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
Debug.Print " " & tdf.Name
Next tdf

End Sub


Now I have a much larger system with 160 tables, 1200 queries, 450
forms and 350 reports. But that would be rather long for folks to
look at the names. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Unbelievable, Tony!!!!!!!

BTW Steve I don't like that attitude of yours. If you are going to
discuss things in the newsgroups please keep a civil keyboard on your
desk.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

BTW Steve I don't like that attitude of yours. If you are going
to discuss things in the newsgroups please keep a civil keyboard
on your desk.

Uh, what Steve wrote looks a lot like what I would have said if he
hadn't gotten there first.

Except, I probably would have been more harsh.

I think, because of the fact that Access presents a combination of
the TableDefs and QueryDefs collections in some contexts, that it's
crucial to distinguish them by a prefix (it could be just t or q),
and because they share a single namespace (i.e., you can't have a
query and a table with the exact same name).

Likewise, I think it's great to be able to have a table and a query
with the same base name, so you can tell that the query presents the
data from a particular table. This is particularly important when
you have ULS and need to use RWOP queries. Your tblPerson would be
edited via the RWOP query qryPerson. That seems to me to be an
extremely logical way of organizing things.

How would you do the same thing without prefixes? Suffixes?