I'm creating an Orchard module that will, among many things, store and provide search and analysis capabilities on a database of bicycles. The use cases are as follows:
- Store a bicycle and data describing it.
- Store multimedia related to the bicycle. This might include photos, video, etc.
- Query the database of bicycles to find the right size based on certain physiological characteristics of the user.
- Query the database of bicycles based on its price.
- Query the database of bicycles based on whether it is in stock.
- Store data synchronized with a separate database (in this case, our Point-of-Sale system which uses SQL Server as its back-end) to determine which bikes are in stock and which are not.
Each bike will have a make, model, year, and size. One make might have multiple models, each model might have multiple sizes, and each model might have different version s from year-to-year which would also have different sizes. In a traditional database, I
would have a Bike table (make), a Bike Configuration table (model), and Bike Geometry table (size). A Bike would have multiple Bike Configurations and Bike Geometry records FK'd to it.
For example, I might have a Felt AR Bicycle, but multiple configurations of a Felt AR for the various components that would be installed on it, so a Felt AR 5 for the version of the bike with Shimano 105 components, and a Felt AR 3 for the version of the bike
with Shimano Ultegra components (and so on).
I'm trying to figure out the best way to represent these data relationiships in Orchard. My initial thought was to have a ContentType called Bicycle made up of Content Parts Bicycle and Bicycle Configuration. That would allow me to have a single Content Item
for each Bike-Bike Configuration pair and give me a place to hang multimedia related to a specific bike and utilize the great media management capabilities Orchard has.
In those Content Parts, I'd just simply store the searchable data and multimedia data related to a bike, and then put the rest of the data in standard SQL Server tables. When displaying a bike, I'd just query the data tables for the data related to it and display
those alongside the Content Item.
Orchard seems poorly suited to store all of the data and represent the more complex data relationships. How have others stored complex data in Orchard and maintained data relationships?
I've spent a few days researching and reading and need some advice on which direction to take. I've been reading Bertrand's "How to Relate Things" series and the "1-n and n-n" database relationships Orchard Documentation article.
I have 4 Content Types that I need to relate:
- Brand (BrandPart)
- Bike (BikePart)
- Bike Configuration (BikeConfigurationPart)
- Geometry (BikeGeometry)
A Brand can have multiple bikes.
A Bike has multiple Bike Configurations and multiple Geometries.
The relationship from Brand <---> Bike
could either be done as a Content Picker on the Bike or a Container (List) on the Brand. I'm leaning towards just using a Container.
The relationships from Bike <---> Bike Geometry
Bike <---> Bike Configuration
are a bit more difficult. Ideally, I'd have two containers on the Bike Content Type - one for Bike Geometries and one for Bike Configurations. But alas, that isn't possible since you can only have a single container
on a Content Type.
So I was considering using a Container on the Bike Content Type to hold Bike Configurations, and then do a 1-N database relationship to relate Bikes to Bike Geometries. I need to make sure the application I'm writing supports a few scenarios:
- Query Bike Geometries by a specific measurements to identify Bikes (and, therefore, Bike Configurations) that would be ideal for purchase.
- Query Bike Configurations by price to identify Bikes (and to look at Bike Geomtries) that would be ideal for purchase.
I probably can
_ do it lots of ways, but with all of the options I have and none fitting perfectly, I'd like one of you more experienced developers to give me an idea of how you might approach this scenario.