Re: Database structure

[Conor Linberg]

DEFINITELY do not design your database that way. Without going into too much detail, that's just a bad idea.

1.
Have a single table that includes unique records about each customer. Name that table CUSTOMER. Make a surrogate key for them and record their first name, last name, address, email, whatever else.

2.
Have a table called STORES where you record all the information about each of the 300+ stores. Have a surrogate key for each unique store and record Name, Address, Branch# or whatever.

3.
Have a table that links STORES and CUSTOMER called STORE_CUST where it has a primary key composed of two foreign keys. The foreign keys will be the primary/surrogate keys from the CUSTOMER and STORES tables. Customers might shop at more than one store so you don't want their data duplicated. This table will take care of that. In addition to the two foreign keys making up the primary key for this STORE_CUST table you can store whatever else is relevant to this transaction such as the transaction number generated at teh store. If that's the case then you'll need another table for recording transactions efficiently.

Do you know enough about database structures to make this work well? you can get yourself in a real bind if the DB isn't built right.

What I suggested is just the framework for what you'll want. I think you'll need something much more extensive to accomplish this.