I am gathering pricing data for an e-commerce site that is dependent on the previous selection. For example:
Product selected (29 choices ) > type of coating (6 choices) > size (12 choices) > sides printed (3 choices- 4:0, 4:1, 4:4) > quantity (15 choices) > PRICE. Then, shipping (6 choices).
I know it seems pretty straightforward, but because it's a tedious job gathering all of the information, I don't want to find out there's a better way to do it or a better format when I hand the spreadsheet over to my developer. What do I need to be mindful of when gathering dependent data that will be dumped into a database and fuel an e-commerce site?
I'm a developer of over 20 years experience and I've been building relational databases the whole time with varying degrees of normalization. I like it best when the problem is given to me as you stated above.
From your statement, it seems like you are trying to design the database. Instead, you hired a developer, and that is what they do. Have him or her design the database. Give them all the information you have and answer all their questions in a timely manner and you'll get what you need.
I'm not sure of your area of expertise or the relationship with and faith you have in your developer, but I would provide him the information from your question above. From that he/she should fire back a bunch of questions to you such as:
1. does every product require every choice?
2. Can a product have multiple options for a given choice?
3. Are the options for choices different dependent on the product?
4. Is there a higher level to consider of product categories?
... I think you get the idea.
Using this dialog can help you both think of concepts or situations you both have missed and provide you with a more robust solution.
I'd be happy to get on a call with you to discuss this further but it sounds like you have everything you need except maybe confidence in your developer(s). Best of luck.
My name is Erik and i'm a Senior Database Architect with more than 15 years of experience and I also have 10 years of experience managing programmers.
The best way to ensure data relations between your objects (Product selected, type of coating, etc) is the following: you should explain to the developer that you want a 100% dynamic parent/child associations between objects without any limits on associations between objects.
If you need more details and/or want to schedule a call, we can go over your business requirements and I can help you build the technical specs for your programmer. I am also available to develop the data structure that you need.
Start with a LAMP Stack - Linux + Apache + MariaDB + PHP.
Be sure to use latest Ubuntu (Linux) as your OS.
Be sure to use latest MariaDB rather than MySQL for your database.
Hire someone who's written a truckload of SQL over decades.
Have them design your database, based on how products will be accessed + searched for, as this will determine speed of your system.
Can't tell you the number of clients I've taken on in my private hosting company, where years worth of code + database design has to be scrapped because it was done incorrectly.
Hire an expert to do this right... the first time + you'll save yourself a huge amount of time + money + you'll be able to sleep well at night.