table setup

[toddstar]

just wanting to ask for advice on the best table layout where customers & suppliers could be both a company or an individual person, with some of the individuals working for a company who is also a customer/supplier?

my thought was to have

table - individual
columns - bog standard details (name, address, etc)

table - company
columns - bog standard details (name, address, etc)

table - individual_company_link
columns - individual_id, company_id

table - product sale
columns - product_id, type, buyer_id
type would be C or I - company or individual
buyer_id - either a reference to the company id or individual id

would anyone recommend a different setup?

 

[jdraw]

You may get some ideas from this model
http://www.databaseanswers.org/data_models/customers_and_orders/index.htm
and this
http://www.databaseanswers.org/data_models/customers_and_products/index.htm

There may be People who work at a Company, companies that are really just individuals ( one person company), ...

I would be cautious with Product_Sale. There are Products, Orders, OrderItems....

Suggest you use a naming convention that does NOT allow/permit spaces or special characters in field and object names.
Limit names to alphanumerics and the underscore "_".

Here are some links to tutorials that will help your learning database and Access.


Principles of relational Design http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials:
If you google for videos "normalization langer" you will find a series of free videos by Dr.Art Langer. These are quite good for learning by Watching/Listening
rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Complete set of tutorials on Acc2010.
https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP

 

[toddstar]

thanks for the links (although the 2nd two links don't work btw) but doesn't really help with the setup for selling to both companies & individuals.

 

[jdraw]

Sorry about the links == not sure what happened with them.

I'll try again

http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

What exactly does this mean "but doesn't really help"?

You have to look at your problem and "abstract" what you can to see a pattern/opportunity.

Let's suppose you sell to a Customer.
A Customer may be a Company, or may be an Individual.

So now you have to identify what makes a Customer a Company or individual?

You may want to Google subtypes/supertypes for info but it may not be necessary to solve your issue.

You could have a Customer table with
CustomerId <--uniquely identifies a Customer regardless of Type
CustomerType <--- identifier to distinguish Company from Individual

then 2 other tables

tblCompany
CompanyId PK <--uniquely identifies Company
CustomerID number FK to Customer table
other Company Info

tblIndividual

IndividualId PK <--uniquely identifies Individual
CustomerId FK to Customer table

There may be other options, but that's what I see quickly.

Good luck with your project.

Here's a link for Entity type/subtype in ER Diagrams
http://it.toolbox.com/blogs/enterprise-solutions/understanding-entities-in-er-diagrams-14255