Question on which keys to use in this example

MarkB423

Okay, I have a shopping cart order system using PHP/MySQL...

The first (main) table is as follows:

invoiceID | orderID | addressID

The next two tables, "orders" and "addresses", will be joined to the main table using one of two methods...

METHOD 1

Table "orders"

orderID | order_info1 | order_info2 | etc.

Table "addresses"

orderID | address_info1 | address_info2 | etc.

METHOD 2

Table "orders"

orderID | order_info1 | order_info2 | etc.

Table "addresses"

addressID | address_info1 | address_info2 | etc.

Obviously, if using Method 1, there is no need for the first (main) table. Currently I am planning on using Method 2, where each bit of information is inserted into its respective table, then the auto_incremented ID is returned and inserted into the proper place in the first (main) table.

I am just curious if Method 1 may be better and result in less fragmented data if an insert into all tables is not successful, despite the fact that it is less normalized.

Keep in mind MySQL has no ROLLBACK capabilities for instances where an insert into a table may fail, which is the only reason I am doubting Method 2.

floR

I would definitely go for method 2. There is no need to directly connect the address data to the order data, other than the join table of invoices. If you keep the addresses separated, you can even upgrade your shopping cart system to one where customers have their own account with address info stored in the database for them to edit and use for future purchases.

I wouldn't worry too much about a failed insert, since you could always 'delete' an entry for which the address insert failed.

Am I overlooking something?

MarkB423

Sounds good to me... the idea that an addresses can be reused is one that had crossed my mind. Also, my only concern with deleting the entry where the insert failed is what if the delete also fails?

Sure, the odds are slim to none, but it is worth considering I guess... I'm sure I could write a simply "database cleanup" query that removes all fragmented data, but I don't think it would be a problem...