Database Redesign/Logic Question

Databases
1

Hey,

I am taking over development of a shopping cart for a music store. The site originally only sold music but now I am tasked to add other items that are not music files (i.e books/posters)

So, the question is, since the entire cart depends on finding product id from the ‘music_tracks’ table, I now am trying to figure out the best way to add other products and have non conflicting product ID’s for books and posters (using these 2 non music products as examples for this post).

So, what should I do? Should I spill all music files into a universal product table and increment from that table and re-write all of the cart files to handle product ID’s from that table?

Obviously if I have a separate table for physical merchandise, the existing cart page would confuse the product ID from that new table with older products from the music table (such as adding new record ID #3, the cart will think it’s ID #3 from the music table)

What is the best way to avoid conflict in your opinion?

Thanks

2

I will use this for the future and will debate how to use this for our site in the long term plans should we see the need for sub categories/et.

The problem is, with the cart working as it does, it’d be a shame to have to re-write it entirely (as well as the db structure) just to add a few non music items. I was wondering if the following idea might work, as the company wants to re-launch quickly.

Step 1: Add New ‘merchandise table’

Step 2: Add new product_type table (music = id #1, poster = id #2, book = id #3 etc)

Step 3: Transfer all product ID’s of music table to merchandise table before adding merchandise such as posters/non music/etc. (there are 0 non-music items for sale now)

Step 4: Then, change the admin panel to allow both music and non music to go into merchandise while saving the ‘product id’ type.

This way, we have one table that holds all products while knowing which type.

After this, I re-write the cart to sniff out product id, which it does, but this time it will do it for the merchandise table and not the music table.

This way, all pages programmed to add a product to the cart, by product id, can still do it, but this time I can also have the power, should I need it, to recognize product TYPE.

It’s less radical than a new database schema and won’t require a ton of re-working of the cart. Does this sound like a logical plan to add merchandise and save time in the re-launch for this company? Am I overlooking anything?

Thank you.

3

Interesting. I will check it out. Ty.

4

Depending on your current database structure, this could be a good opportunity to rebuild your database based on the Nested Set Model to organise your categories and products. Imho this model makes it a lot easier to have infinite categories and levels of sub-categories.