table building

[pwicr]

I have a table of people who have requested brochures.

If they actually PURCHASE our product should they go into a separate table called tblOWNERS in which I also want it to look at the list of people who requested brochures. if not where would I add them so I don't have duplicate information stored in multiple tables.

or

should I just have a the product ID number as a field in the brochure request table?

trying to figure out the best way to build this. I am actually REBUILDING our database as it was set up incorrectly. now I've been asked to find out how many brochure requests last year resulted in a purchase....

 

[boblarson]

You should not have separate tables for those people. You can, and should, store them together. You would simply have a status that you would set to be able to distinguish between the two.

 

[pwicr]

would the product ID be an ok status indicator? they need to be able to have multiple product id's under each should they purchase multiple over the years.

 

[boblarson]

If multiple product ID's are required, then you would need another table to store

tblPersonsProducts
PersonID - Long Integer (FK from Persons table)
ProductID - Long Integer (FK from Products table)

And anything applicable to that product ID like date sent, etc.

The status for them could either be something stored in the Persons table or simply calculated by the existence of a record in the tblPersonsProducts table.

 

[pwicr]

hence the tblOWNERS?

but if the new owner ISN'T in brochure requests then they are added to the brochure requests table (which is actually called tblCUSTOMERS)

then added to tblOWNERS as a product purchaser.

 

[pwicr]

question #2 re: tblCUSTOMERS. How do I handle the people who have requested brochures multiple times? perhaps every few months or years. I would end up having duplicates in my table.

 

[pwicr]

tblOWNERS is currently

OWNERID (PK)
LEADID (FK - tblCUSTOMERS)
CHVINID (FK - tblCHASSIS)
NUMRETDATE (Numerical Retail Date)

 

[boblarson]

hence the tblOWNERS?

but if the new owner ISN'T in brochure requests then they are added to the brochure requests table (which is actually called tblCUSTOMERS)

then added to tblOWNERS as a product purchaser.

You lost me on that one. The structure as I see it is this (and I don't know your business so I can't state with 100% accuracy this but here goes - instead of using Customers and Owners, use Persons.

tblPersons
PersonID - Autonumber (PK)
FirstName
LastName
Address
City
Region
PostCode

tblProducts
ProductID - Autonumber (PK)
ProductDescription

tblPersonsProducts
PersonProductID - Autonumber (PK)
PersonID - Long Integer (FK)
ProductID - Long Integer (FK)


Remember, whether they are potential customers or they are customers they are still people and they have common attributes. The only thing that distinguishes them from either other is whether they have purchased and you also want to know whether they have been sent a brochure. So, Brochure can be a product in the products table. You can have product types so that it can be determined quite easily if the product sent was a brochure or actual product.

 

[pwicr]

Yes, that is basically what i have. we sell motorhomes so I have my Vehicle ID number as my PK in it's table and FK in all other tables I need the VIN to link to such as who purchased it.

I can't see how I can work in brochures as product.

 

[pwicr]

OH. and how do i handle a change of address of a customer? I want it to flow as the same customer but keep the address history?