How to normalize this?

  • Thread starter Thread starter Applebaum
  • Start date Start date
A

Applebaum

Hello newsgroupers,

I am modifying a database of Purchase Orders. It's fairly straightforward
right now, with a tblPurchaseOrders containing fldVendor. I'd like to
separate out the vendors into their own table, so that a) they're normalized
and b) the vendor can be added to a purchase order using a combo box.

A vendor can be either:
1. A company with no contact person (ACME)
2. A person with no company (Jane Doe), or
3. A person at a company (John Doe at ACME).

What table structure should I use for this?

If I use a single tblVendors then fldFirstName, fldLastName and
fldCompanyName can't all be required. And fldCompanyName can't be unique
with no duplicates.

If I separate vendors into a tblCompanies and tblPeople (presumably with
tblPeople having the foreign key fldCompanyID), how do I relate this back to
tblPurchaseOrders, with its foreign key of fldVendorID?

Many thanks in advance,

Matthew
 
You have to be careful placing Organizations and Persons in the same table.

You are running into a basic problem where the attributes list associated
with an Organization is different than the attributes list associated with a
Person. This is just the beginning of the differences. Organizations
typically have business units you purchase from and separate business units
that you pay, where you purchase from and pay a Person.

If I were building a PurchaseOrder application, I would employ the following
tables:

PurchaseOrder (table)

Id - primary key - unique - long integer - indexed
VendorType = "Organization" or "Person" - this tells you what to store and
retrieve
Date
TotalAmount
SpecialInstructions
etc.

Organization (table)
Id - primary key -unique - long integer - indexed
Name - secondary key - unique - indexed
Street
Phone
etc.

Person (table)
Id - primary key -unique - long integer - indexed
Name - secondary key - unique - indexed (ex: "Jones, James J.) if two
Jones's then ("Jones, James J., Philedelphia")
Street
Phone
etc.

Item (table)
Id - primary key -unique - long integer - indexed
Name - secondary key - unique - indexed
Package
Cost
Size
Shape
etc.

PurchaseOrderOrganization - if the seller is an organization use this
relationship

PurchaseOrder.Id - foreign key - primary key element - not unique - long
integer - indexed
Organization.Id - foreign key - primary key element - not unique - long
integer - indexed
Relationship - values = "Seller, Insurer, Shipper, etc

PurchaseOrderPerson - if the seller is a person use this relationship

PurchaseOrder.Id - foreign key - primary key element - not unique - long
integer - indexed
Person.Id - foreign key - primary key element - not unique - long integer -
indexed
Relationship - values = "Seller, Contact, etc

PurchaseOrder.Item - this handles the one to many from PurchaseOrder to
PurchaseOrder.LineItem

PurchaseOrder.Id - foreign key - primary key element - not unique - long
integer - indexed
Item.Id - foreign key - primary key element - not unique - long integer -
indexed
QuantityPurchased
PurchasePrice
Discount
etc.

Regards . . .
 
Hi,

I forgot to add: if both an Organization and a Person are related to the
PurchaseOrder, the VendorType = "Both" and you store and retrieve two
relationships.

Sorry about that . . .
 
I really like the simplicity of this solution.

The bummer about it is that I can't have a person at a corporation, unless I
modify it so that there's another field:

IsCorporate Yes/No
CompanyName Text
FirstName Text
LastName Text

When IsCorporate is No, CompanyName could be hidden/disabled.

The source of the combo box on the purchase order form could be a query
displaying "CompanyName: LastName, FirstName" or if it's not corporate:
"LastName, FirstName"

Is this an efficient/normal structure? How might it backfire?

Many thanks for your solution,

Matthew
 
This is super, thank you!

Matthew

Parker Shannon said:
You have to be careful placing Organizations and Persons in the same table.

You are running into a basic problem where the attributes list associated
with an Organization is different than the attributes list associated with a
Person. This is just the beginning of the differences. Organizations
typically have business units you purchase from and separate business units
that you pay, where you purchase from and pay a Person.

If I were building a PurchaseOrder application, I would employ the following
tables:

PurchaseOrder (table)

Id - primary key - unique - long integer - indexed
VendorType = "Organization" or "Person" - this tells you what to store and
retrieve
Date
TotalAmount
SpecialInstructions
etc.

Organization (table)
Id - primary key -unique - long integer - indexed
Name - secondary key - unique - indexed
Street
Phone
etc.

Person (table)
Id - primary key -unique - long integer - indexed
Name - secondary key - unique - indexed (ex: "Jones, James J.) if two
Jones's then ("Jones, James J., Philedelphia")
Street
Phone
etc.

Item (table)
Id - primary key -unique - long integer - indexed
Name - secondary key - unique - indexed
Package
Cost
Size
Shape
etc.

PurchaseOrderOrganization - if the seller is an organization use this
relationship

PurchaseOrder.Id - foreign key - primary key element - not unique - long
integer - indexed
Organization.Id - foreign key - primary key element - not unique - long
integer - indexed
Relationship - values = "Seller, Insurer, Shipper, etc

PurchaseOrderPerson - if the seller is a person use this relationship

PurchaseOrder.Id - foreign key - primary key element - not unique - long
integer - indexed
Person.Id - foreign key - primary key element - not unique - long integer -
indexed
Relationship - values = "Seller, Contact, etc

PurchaseOrder.Item - this handles the one to many from PurchaseOrder to
PurchaseOrder.LineItem

PurchaseOrder.Id - foreign key - primary key element - not unique - long
integer - indexed
Item.Id - foreign key - primary key element - not unique - long integer -
indexed
QuantityPurchased
PurchasePrice
Discount
etc.

Regards . . .

back
Click to expand...
 
Hmm. Not sure about that.

I'll wager you will want to associate more than one person with a company at
some stage during the life of this database, and the structure won't be up
to it. If you want to do that, you may be better working with the two
tables, and defining a one-to-many relationship between them.

I did a contact database once where a client (corporate or individual) could
be the parent of other clients. This involved a link table between two
copies of the same table. The client's record (main form) had subforms to
show any parents and children of this record, and you could double-click the
subform record to load it into the main form. It was incredibly flexible.
The only problem I had was when the guy wanted a flat-file print out of data
that is effecively recursive. :-)
 
Back
Top