If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Best Practice for tables?
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 |
#3
|
|||
|
|||
Best Practice for tables?
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 |
#4
|
|||
|
|||
Best Practice for tables?
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 |
#5
|
|||
|
|||
Best Practice for tables? - Steveo is still soliciting!
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 |
#6
|
|||
|
|||
Best Practice for tables?
"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 |
#7
|
|||
|
|||
Best Practice for tables?
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/ |
#8
|
|||
|
|||
Best Practice for tables?
"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/ |
#10
|
|||
|
|||
Best Practice for tables?
"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/ |
Thread Tools | |
Display Modes | |
|
|