Solved

Access - Table Design related to creating a Classification Type and Classification SubType

Posted on 2013-01-06
6
331 Views
Last Modified: 2013-01-21
Hello All

As most all of you know I am "Hacking" around at this whole Access thing.  With your all's Expert help I have learned a tremendous amount.  Thank you.

Now then I am considering/pondering how to break down some information and am contemplating using a Classification Type and a Classification Sub Type.

For the purpose of this discussion, I will use an Example framed like say Suppliers....

So let us say I have a Table called
dta_Suppliers

In that Table I would have fields
SupplierID - PK Autonumber
SupplierCode - Text
SupplierName
etc....

Now then, in order to Classify Suppliers into various groups I envision a Table called
SupplierType

In that Table I envision fields
SupplierTypeID - PK - Autonumber
SupplierType - Text

HOWEVER I would also like to have SubType Classification ability.  So therefore I envision creating a Table Called
SupplierTypeSub

In that Table I envision fields
SupplierTypeSubID - PK - Autonumber
SupplierTypeSub - Text


That all is simple enough.  I could just put a field in the Supplier Table for each Type and TypeSub.  However, if I do that I know I'll end up with all kinds of Combinations of Type and TypeSub which would probably render the info useless in the long run.

SO, I thought about Controlling that by creating a Many To Many table called
ctl_SupplierTypeToTypeSub

In that table I would have:
SupplierTypeID - PK
SupplierTypeSubID - PK

HOWEVER if I do it that way, I presume that I would then either I have to put a record called {None} or {N/A} in my SupplierTypeSub table and every time a new SupplierType classification gets created I would have to populate the ctl_SupplierTypeToTypeSub table with that new SupplierType and the {None} TypeSub. (Again, my presumption but not 100% confident I'm right in this presumption)

If I don't use this Many-Many type table method but just simply have two fields within the Supplier Table 1 for SupplierType and then 1 for SupplierTypeSub Is there a way in the SQL of the SELECT statement for SupplierTypeSub that would limit the user to selecting a TypeSub based on what Management has defined as the way they want the SupplierType to SupplierTypeSub to relate?

So my question is this:
Based on what I am attempting, from your all's experience is there one method preferrable long-term over another. And is my thinking correct here at all?
0
Comment
Question by:wlwebb
  • 2
  • 2
  • 2
6 Comments
 
LVL 39

Expert Comment

by:als315
ID: 38748933
Usually Many-to-Many table is used near beginning, ie when one supplier has many subtypes and they are joined to Type.
For example, "SupplierA" has  subtypes: "Water", "Wine", "Beer", when "SupplierB" has only one subtype "Water".
Table Supplier-Subtype has both IDs (Supplier and subtype) and in index both IDs are included.
Table for SubType has FK from table Types, where all subtypes above joined to type "Drinks"

Try to describe real task, design may vary, but you should follow physics.
0
 
LVL 57
ID: 38749150
<<Based on what I am attempting, from your all's experience is there one method preferrable long-term over another. And is my thinking correct here at all? >>

 Well let's start with some examples of what you mean by type and subtype...

 The questions that need to be answered are:

1. Can a vendor have more then one type? (yes)
2. Can a type have no sub types or must it always have one?
3. Can a type have more then one sub type?
4. Can the same sub-type apply to more then one type?  

 For example: vendor type is "Pool Supplies" and another is "Fish Tanks".  Sub type in both cases is "water".

  You need to say if the sub-type relates specifically to the type, or if a sub type is just another classifcation of vendors rather then types.

Jim.
0
 

Author Comment

by:wlwebb
ID: 38749261
Ok I see your points...  

So let us change then that it is Addresses....

An Address can have many Types and Subtypes..... The Types can have Many subtypes

For Example

If I have a one table of addresses and I want to relate that table to Contacts and Vendors and Employees etc....

So then my Address Types could be

Home Address
Business Address
Physical Address
Mailing Address
Alt Mailing Address
Ship To Address
AP Address
AR Address


Home Address could be a subtype of
Resident Home
Vacation Home
Winter Home
Summer Home
etc...

Business Address Subtypes could be
Corporate Headquarters
Branch Office
Main Warehouse
Branch Warehouse
Accounts Payable Processing
Accounts Receivable Processing
etc...

So if I want to maintain addresses separate from essentially the Contact but classify them whether they are Contacts or Customers or Vendors or Inventory Suppliers or Employees etc

So to answer the question then, for Each of those essentially Contact types then each could in fact have multiple addresses with multiple Address Types and the Address Type could have Address Sub Types.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 39

Accepted Solution

by:
als315 earned 250 total points
ID: 38749767
For this case ou may have following tables:
1. Contacts(Vendors, Employees etc):
ContactID (PK), Name, etc
2. Addresses
AddressID (PK), Index, City, Street etc.
3. Address Type
AddrTypeID (PK), AddrType
4. Address subtype
AddrSubTypeID (PK), AddrSubType
5. Contact-Address-Type-Subtype
ContactID (FK), AddressID (FK), AddrTypeID (FK), AddrSubTypeID (FK)

Now you should think about reports and forms, where these addresses will be used. If you like to select address and look at all contacts in it, this structure will be good, but more often you are selecting contact and need some specific address (mail, for example). In this case, if a list of Address Types are fixed, you may have these fields in table contacts:
Contacts(Vendors, Employees etc):
ContactID (PK), Name, Home Address, Business Address, Physical Address etc.

I don't understand how you like to use subtypes like: Resident Home, Vacation Home etc. You should also have some table with details about it (vacations started from-to etc)
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 38750796
I'm not sure I see the need for the sub-type.   Some of your sub-types are duplicating types (Accounts Payable Processing, Accounts Receivable Processing).   What I do see is that your thinking addresses are in of themselves something seperate that stands alone from a contact.

For example, an address may be for a company's Corporate headquaters.  For one employee, that may be their business address and their mailing address.  For another, it may be their business address, but they use another address as their mailing address because they mainly work from home.

So I end up with this:

tblContacts
ContactID (PK), Name

tblAddresses
AddressID (PK), City, Street, Zip, Phone, Fax

tblAddressTypes
AddrTypeID (PK), AddrTypeDesc

tblContactAddresses
ContactID (PK1), AddressID(PK2), AddrTypeID(PK3)


which now that I look back, is the same thing more or less as what Anthony had given you.

I think I'd follow KISS though and keep it simple.

Jim.
0
 

Author Closing Comment

by:wlwebb
ID: 38802450
Thank you both.  I have some more pondering to do on how far I want to take this...
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question