To prefix this, I am stuck using Microsoft Access, or at least relating to it, so my capabilities are a bit limited. I also do not want to use an EAV model. I could put the new tables into SQL Server, but it would require software driven integrity, and that makes me a bit nervous.
Some of the business rules I have to accommodate for:
- Products must have multiple price levels.
- Products can be stand alone and/or part of a bundle of products.
- A "product" can also be a service. Such as education.
- Bundles themselves are not a tangible product, they only contain products and will have a $0.00 price.
- Products can also be part of a package of products. The products may have a different price inside of the package.
- Packages can also have bundles of products in them. These packages can also have their own special prices for both the individual products and the products inside of bundles.
- Packages have several "Steps" that require a specific product or bundle. The user must pick an item defined for that step. Think about designing your own vehicle and the "Package" is a truck. The "steps" with variable products would be things like what color body do you want for your truck or what shape body of the truck do you want. The products, respectfully, would be things like blue and green or small, medium and large. Each option is a different product. Stuff like that, that defines the package.
Here is an idea I had:
The problems with this model are:
- I cannot have multiple prices for bundles in this model. (E.G., we sell a bundle to resller A for price X and resller B for price Y).
- Packages cannot override bundle prices in this model.
- Querying seems a bit difficult, as it is reciprocal.
I looked at Magento's ERD, and would much prefer a relational model database. I do not want to use EAV, as I feel that would be overkill for what we need anyway, and seems like a real headache to deal with.
Do you have any ideas or suggestions?
This model is my most current concept. Each product will have options such as a red, blue and green pencil. The unit cost for that item will be in tblProductOptions and the adjustment to that price will be in tblPrices.