How to structure my database.

edwa5823

Good afternoon all,

Well I have been programming in php for about a year now, and I am just now starting to understand the usefulness of a database as oppossed to keeping everything in $_SESSION or arrays.

I am in the process right now of creating an ordering system for a small photography studio that I work for, but am a little lost when it comes to the database structure. Here is my problem:

Each customer will have an account created once they have their first sitting at the studio. Upon logging in a list of available galleries is presented to them (Each gallery representing each different sitting they have had). They will select the gallery to view and then will be shown proofs of each picture in that gallery.

From there they will tell me how many picture of each size they want to order of each proof. As you can see I can getting into a lot of different variables here.

I want to be able to display purchase history by user and by order number, so here is where the problem stands.

Should I create all of the users purchase history inside one table of my current database, then figure out some way of sorting by user id. order number, proof number, and size until I get a useful account history.

Or should I create a second database called "orders" then for every customer create a table then try to make each order as it's own row?

Any guidance on this topic would be greatly appreciate since I am still learning how to do this.

The biggest problem is that each individual proof could have up to 20 different variables, and the number of proofs per order will change.. So I can't hard code anything except the print sizes available to order. Everything else has to be somewhat dynamic.

Thanks a ton in advance,

edwa5823

If you would like to see an example you can go to the following link: http://www.agmillerstudios.com/accounts/login.php

Use the name "php" and password "builder".. Follow the link that says "Order Test" and there you will see the options for ordering.

FMB

with reading the above, I'd say you will need the following:

Table: CUSTOMER
Main key: CUSTOMER_ID

Table: Galleries
Main Key: GALLERY_ID

Table: PROOFS
Main Key: PROOF_ID
2nd Key: GALLERY_ID (to link to the Galleries table)

Table: CUST_GAL
Main Key: CUSTOMER_ID (to link to table CUSTOMER
2nd Key: GALLERY_ID (to link to table GALLERIES)

Table: ORDERS
Main Key: ORDER_ID
2nd key : CUSTOMER_ID (to link ot table CUSTOMERS)
3rd key: PROOF_ID (to link to table PROOFS)

With these you can link the tables inside SQL Queries and get abou all results you wish.

TVBanks98

edwa5823 ,

The idea when designing a database is to have information that is related together stored together in a single table. You also want to design the relationships between the individual tables so that you don't have redundant date accross multiple tables..... with the exception of key data. Your "store" has three logical groups of data: customer data, gallery data and order data.

I think you can do this pretty easily with three tables, one for each logical group of data:

Table: CUSTOMER
COLUMN_1: CUSTOMER_ID
COLUMN_2: Name
COLUMN_3: Address
COLUMN_4....... etc, etc
Primary Key: CUSTOMER_ID

Table: GALLERY
COLUMN_1: CUSTOMER_ID - relates to CUSTOMER.CUSTOMER_ID
COLUMN_2: GALLERY_ID
COLUMN_3: PROOF_ID
COLUMN_4: Sitting_Date
COLUMN_5: Any other gallery-specific data .... etc etc etc
Primary Key: CUSTOMER_ID, GALLERY_ID

Table: ORDERS
COLUMN_1: ORDER_ID
COLUMN_2: CUSTOMER_ID - relates to CUSTOMER.CUSTOMER_ID
COLUMN_3: GALLERY_ID - relates to GALLERY.GALLERY_ID
COLUMN_4: PROOF_ID
COLUMN_5: Any other order-specific data .... etc etc etc
Primary Key: ORDER_ID
Non-unique Index : CUSTOMER_ID, GALLERY_ID, PROOF_ID

This option gives you the ability to join your tables and order the result set to suit your fancy.

Best of luck !!
TVB
😃