Best Practice for tables?

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

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Kamitsukenu" wrote in message
...
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

Hi Arvin,

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

Kind Regards,

John


"Arvin Meyer [MVP]" wrote:

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

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




"Kamitsukenu" wrote in message
...
Hi Arvin,

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

Kind Regards,

John


"Arvin Meyer [MVP]" wrote:

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

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

"Steve" wrote in message
...
FYI in case you are interested ---

A service I provide is to.....
PC Datasheet

"Steve" schreef in bericht ...

FYI in case you are interested ---
100 First Time visitors only last week, 235 pageloads

You certainly get a lot of attention Steve...
Hmmm... do you also get lots of new customers ??
--
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3500+ pageloads, 2300+ first-time visitors (these figures are rapidly increasing)

Arno R

Kamitsukenu wrote:

(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/

"Steve" wrote:

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/

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






"Tony Toews [MVP]" wrote in message
...
Kamitsukenu wrote:

(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/

"Steve" wrote:

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/