Question about EXTENDED DETAILS tables

[sjs94704]

I am literally at the very beginning of my database planning. As a result of another thread I got turned onto an article with some interesting ideasfor database structure so I decided I am going to use at least part of it.

So, to have less tables, all customers, vendors, contacts and anoth OTHER contacts are called a PARTY. So, the first table looks something like this:

NOTE: This is just the start of the table. There will no doubt be more fields Added!

PKPartyID (PK for Primary Key)
NameFunctionID (Customer, Vendor, Contractor, Contact, etc...)
FirstUsedDate
LastUsedDate
Surname
RestOfName
Salutation
IsActive

The plan is that absolutely EVERYTHING else branches off of this table ....

QUESTION:

For extended details they will no doubt be much different for individuals vs vendors vs contacts.
Let me be clear, by extended details, I am NOT talking about address, phone, emails, etc...
I am talking about everything other than all of that which is specific to each tpe of party ..

So, what is the best way to hand the extended details for each party as far as table structure?

 

[CJ_London]

So, to have less tables, all customers, vendors, contacts and anoth OTHER contacts are called a PARTY.

Having less tables should not be an objective of your design in itself, you could end up with a very inefficient design.

Your question is a bit like asking 'How to get from London to Edinburgh' - there are multiple answers and a best answer depends on knowing things like when? how many people? how much luggage? budget? how fast? any travel phobia's? etc

So depends how much variation since some details will be common or are basically the same depending on function - and don’t forget some parties could fulfil more than one function and perhaps not at the same time.

you could include a table for each function type linked to your party table on partyID and functionID - so 'Salutation' would be in that table for a contact but would not necessarily be relevant to a customer or supplier.

Alternatively have a table of detail types much like you would use for contact methods

Or a combination of the two

And perhaps there is a need for a self referencing field for example a contact is a contact of a customer? supplier? employee?

So there are multiple ways - but which is the best depends on the requirement of the application, I don't think there s a one size fits all.

 

[LarryE]

Can a single "Party" be a customer and a vendor and a contact and a contractor or any combination?

If yes, you will need fields in the table for those types. You can use TypeCustomer, TypeVendor, TypeContact and TypeContractor to name the fields and use a Yes/No Data Type for each. Then on your input form for "Party", you would use a checkbox control for each type bound to the related field so each Type is checked or unchecked.

If no, then use a single field named 'PartyType' with a Data Type Number. Then on your input form, use an Option control. The wizard will assign a number for each PartyType and allow only one selection for each record.

Once you decide which method you like, then I would use a ContactInfo table linked to your Party table because each Party can have more than one address, phone and e-mail depending on their Party Type. they could have a personal street address and a contractor business address for example. They could also have a personal e-mail and business e-mail. So I would keep this information in a separate table. Doing it this way facilitates easier filtering and searching later on.

Next, for customer orders you will need:

1. A CustPO table to record Purchase Order dates and numbers linked to your Party table
2. A CustPODetail table to record each CustPO line item linked to your CustPO table
3. An Inventory table (if you keep an inventory of items for sale) linked to your CustPODetail table for products sold
4. A CustShipment table for products shipped with shipping dates and shipment method linked to your Inventory table

This is just one example of how to think about designing an ACCESS app based on what you have said.

 

[sonic8]

Once you decide which method you like, then I would use a ContactInfo table linked to your Party table because each Party can have more than one address, phone and e-mail depending on their Party Type. they could have a personal street address and a contractor business address for example. They could also have a personal e-mail and business e-mail. So I would keep this information in a separate table. Doing it this way facilitates easier filtering and searching later on.

I think this would make filtering/searching harder in many cases. At least it's not a significant improvement.

But, more importantly, I don't agree with your assumption about additional attributes being party type dependent in principal.
Why would one contact have more than one address/phone/email depending on its party type? Why would you, in principal, care about the personal address of a contractor but not about the personal address of a customer? - I can't imagine any scenario for that.

If multiple attributes like address/phone/email may be required for some contacts, these attributes should be generally put into their own tables, regardless of party type.

 

[sonic8]

I am talking about everything other than all of that which is specific to each tpe of party ..

So, what is the best way to hand the extended details for each party as far as table structure?

Impossible to say without you explaining what type of extended details you think about.

Other major entities in the database such as invoices, purchase orders, etc. must exist anyway and will reference one (or more) record from your party table to identify the relevant party. - You can apply this principle to all other data as well. I don't think there is much need for any special treatment of certain types of information.

 

[LarryE]

Why would one contact have more than one address/phone/email depending on its party type?

Because some people have more than one depending on their needs.

Why would you, in principal, care about the personal address of a contractor but not about the personal address of a customer? - I can't imagine any scenario for that.

I don't and most certainly did not imply that.

If multiple attributes like address/phone/email may be required for some contacts, these attributes should be generally put into their own tables, regardless of party type.

That's exactly what I was trying to say. Guess I didn't say it well enough.

 

[sjs94704]

Impossible to say without you explaining what type of extended details you think about.

Other major entities in the database such as invoices, purchase orders, etc. must exist anyway and will reference one (or more) record from your party table to identify the relevant party. - You can apply this principle to all other data as well. I don't think there is much need for any special treatment of certain types of information.

What I mean is, for example, the extended details for a PERSON might include things like birthdate, Social Security Number, State ID # (Drivers License or State ID, etc.) and so on about an individual.

The extended details for a contractor might include things license #, ServiceID (Can select more than one service that is provided), along with any other fields related specific to a contractor but has nothing to do with a person or a vendor.

The extended details for a vendor might include things license #, ProductCatagoryID (Can select more than one product category that is provided), along with any other fields related specific to a vendor but has nothing to do with a person or a contractor.

For my initial design I am only going to have a few of these to start with, but I want to build the DB so that I can easily modify this to accomodate other types to fit the clients needs when the time comes to distribute a copy to a client. If there is a way to make it so that the client can add more choices on their own where dealing with all these table values are handle via VBA somehow, GREAT!!!

BUT, for the example listed above, would that require seperate tables for each TYPE for their exended details, OR, could it be possible to organize everything in one single table and then display what is needed using queries depending on the type that is selected?

 

[LarryE]

What I mean is, for example, the extended details for a PERSON might include things like birthdate, Social Security Number, State ID # (Drivers License or State ID, etc.) and so on about an individual.

The extended details for a contractor might include things license #, ServiceID (Can select more than one service that is provided), along with any other fields related specific to a contractor but has nothing to do with a person or a vendor.

The extended details for a vendor might include things license #, ProductCatagoryID (Can select more than one product category that is provided), along with any other fields related specific to a vendor but has nothing to do with a person or a contractor.

For my initial design I am only going to have a few of these to start with, but I want to build the DB so that I can easily modify this to accomodate other types to fit the clients needs when the time comes to distribute a copy to a client. If there is a way to make it so that the client can add more choices on their own where dealing with all these table values are handle via VBA somehow, GREAT!!!

BUT, for the example listed above, would that require seperate tables for each TYPE for their exended details, OR, could it be possible to organize everything in one single table and then display what is needed using queries depending on the type that is selected?

What exactly is the purpose of this application? Are you attempting to build a One-size-fits-all application to distribute to other companies or people? Is that what this is supposed to be?

 

[sjs94704]

What exactly is the purpose of this application? Are you attempting to build a One-size-fits-all application to distribute to other companies or people? Is that what this is supposed to be?

Good Question.

I wrote a Work Order Database previously, but it was quite basic in nature, so it is my intent to expand and improve on that, starting with trying to streamline (I guess that is the right word) the number of tables I use and try to learn a lot from my previous experience with that.

I have a good handle on the relational database concept, but I am really just attempting to think this part through better so I don't have for example sake 50 tables of data when 10 would be just fine!

Let me be clear, as far as table structure and queries my skills need LOTS of learning. That is why I am here talking about this now. What I am much better at is making the forms look nice.

I think my previous database had a lot more tables than I needed..

For example, I had seperate tables for each type of 'party' vs putting them all together in one table. Then there was seperate tables for phone numbers for each type of 'party' as well as addresses too etc..... all because I did not understand at the time about proper flaging within each of these tables so I would only need ONE table for each!

 

[jdraw]

You might want to watch this video by Dr.Daniel Soper on Super and SubTypes, and/or search on this topic for some examples.
Also, before getting too deep into physical database, you might get some insight from "stump the model".

 

[LarryE]

Well ACCESS application design always follows some kind of operational workflow and designed according to what you wish to track. If you have customers that buy stuff, then you need a customer table, a PO table, a PO Details table, an Inventory table and a shipments table. If you have vendors you buy stuff from, then you need a vendor table, a VendorPO table, and so on. If you are simply keeping track of addresses and phones etc for contractors or contact information, then it is more simplified. So design it to what you wish to track. I think CJ was correct. Don't worry right now about the number of tables.

You menthioned earlier about allowing the client to enter their own "Type". If that's the case then you need a PartyType field that does not have fixed options, but allows new Types as well as selecting current types. A combo box that allows both is OK for that.

A warning though. If you are trying to develop something that is everything to everyone, it will get complicated very quickly.

 

[sjs94704]

A warning though. If you are trying to develop something that is everything to everyone, it will get complicated very quickly.

I hear you. The video that I was referred to earlier looks cool. It goes into much deeper detail of database design theory than I have seen before which is really helpful. So, jdraw, thanks for referring me to that!

 

[Pat Hartman]

Well ACCESS application design always follows some kind of operational workflow and designed according to what you wish to track.

This isn't an Access problem. It is an application design problem that is independent of the implementation tool. You use the same techniques to normalize your tables when you are implementing using Access/ACE or C++/SQL Server or COBOL/DB2.

 

[sonic8]

What I mean is, for example, the extended details for a PERSON might include things like birthdate, Social Security Number, State ID # (Drivers License or State ID, etc.) and so on about an individual.

The extended details for a contractor might include things license #, ServiceID (Can select more than one service that is provided), along with any other fields related specific to a contractor but has nothing to do with a person or a vendor.

The extended details for a vendor might include things license #, ProductCatagoryID (Can select more than one product category that is provided), along with any other fields related specific to a vendor but has nothing to do with a person or a contractor.

A Party is probably not the same as a Person. A Party might have just one Person (i.e. *is* that Person) or it might have N Persons; e.g. employees of a company (supplier or customer). Technically that can be solved by creating a Person table that is in a 1:N relation to the Party table.

What about a Party offering products *and* services? Anyways, just create a Services and a ProductCategory table (maybe these might also fit well into a just a single table) and then link these tables to the Party in another 1:N relation.

Your customer of today might become your supplier of tomorrow.

My conclusion here: Sure, there will be detail tables that are *usually* only relevant for one particular PartyType, but still design the table structure and their relations in a general/generic way.

Reverse perspective: It's *not* that a Party is a supplier and only therefor can offer products. It's the other way round: A Party is offering products and therefor can function as a supplier.
I think, using this perspective will lead to a much more open and flexible design.