Defining the Database Structure for the Pricing Wizard
Option A
We are working on a small project that requires a custom table pricing wizard. (yes, the actual custom tables are the kind you eat. From here on, I'll call them kitchen tables so we don't get confused). I came up with a model where every part of the kitchen table was a database table. So the database looked like this:
TableLineItem
-------------
ID
TableSizeID
TableEdgeWoodID
TableBaseID
Quantity
TableEdgeWoodID
---------------
ID
Name
MaterialUnitCost
LaborSetupHours
LaborWorkHours
Each part should be able to calculate its price. Most of the calculations are very similar. I liked this structure because I can drag it right into the linq-to-sql constructor and create all my classes. (Less coding means less to maintain ...) Then I implement a cost calculation interface that just takes the size of the table. I've written some tests and it works really well. I also added an added table to filter details in the UI based on previous selections. (You may not have a specific wood with a specific finish.) There are some other exceptions in the model, and I've hardcoded them. This model is very rigid and changing requirements would change the data. (For example, if all the tables suddenly need umbrellas.)
Option B:
After various meetings with my colleagues (which probably took more time than the size of this project should be considered), my colleagues decided that they would prefer a more general approach. Something like that:
Spec
----
SpecID
SpecTypeID
TableType_LookupID
Name
MaterialUnitCost
LaborSetupHours
LaborWorkHours
SpecType
--------
SpecTypeID
ParentSpecType_SpecTypeID
IsCustomerOption
IsRequiredCustomerOption
etc...
This is a much more general approach that can be used to build any product. (for example if they started selling chairs ...) I think it will take longer to implement, but will be more flexible in the future. (although I doubt we'll look at this again.) Also you'll lose some referential integrity - you'll need triggers to ensure that the table table can't be set for the table tree.
Questions:
- Which database structure do you prefer? Feel free to suggest your own.
- What is considered best practice? If you have multiple similar database tables, are you creating 1 database table with a type column or multiple separate tables? I suspect the answer starts with "It depends ..."
- What will be the difference in time for the two sets (1 week, 1 day, 150% longer, etc.).
Thanks in advance. Let me know if you have any questions so I can update this.
a source to share
I don't have time for a complete answer right now, but I'll throw this out:
- Generally, it is a bad idea to design a database based on the development tool you are using to code it.
- You want to be general to the point . The tables in the database must represent something and you can make it too general. For example, a table called "Things" is probably too general.
- You may be able to make restrictions beyond what you expect. Your "base table" example with a "table tree" doesn't make sense to me, but if you can expand on it with a specific example, someone can help with that.
- Finally, if it’s a small application for one store, then your project will have much less impact on the outcome of the project than if you were developing an application that would be heavily used and constantly changing. This goes back to the "too general" comment above. You can redefine the system when its use is minimal and well defined. Hope this makes sense.
Considering your comment below about base tables and forests, you can customize a table called TableAttributes (or something similar) and each possible option will have a specific type of table attribute. You can then enforce that any given parameter is only used for the attribute to which it applies by all foreign keys.
a source to share
There is a tendency to be overly abstracted in database schema design as the cost of changes can be high. Myself, I like the table names which are pretty descriptive. I often equate schema with OO design. For example, you would normally not create a class named Thing, you would name it Product, Furniture, Item, whatever is relevant to your business.
The schema you specified has a mix of abstract (spec) and concrete (TableType_LookupID). I would tend to flatten the abstraction layer, so use objects like:
ProductGroup (for the case where you have a product that is a collection of other products)
Product
ProductType
ProductDetail
ProductDetailType
etc.
a source to share
Here's what my experience would tell me:
- Which database structure do you prefer? Without a doubt, I would take the approach. Go to the simplest setup that can work. If you add complexity, always ask yourself, what value will it have for the client?
- What is considered best practice? ... It really depends, in part, on the size of the project and the expected rate of change. Generally, generic tables are worth it when you expect the client to add new types. For example, if your client wants to add a new "colored" object to a table, you will need generic tables. You cannot predict in advance what they will add.
- What will be the time difference between the two sets? Without knowing your business, skill and environment, it is impossible to make a valid assessment . The approach you are confident about coding will take the least time. Here, in my opinion, approach # 1 can be 5x-50x just as fast. Shared tables are complex, both in the database and on the client side.
a source to share
Option B ..
Generic is usually better than specific. Software is already doomed to failure or access to its capabilities due to its design for a specific set of tasks. If you build something in common, it will collapse if you abstract away with a realistic analysis of where it might start. As long as you avoid over-abstraction and under-abstraction, this is probably the sweet spot.
In this case, the adage "less code more" is likely to be drawn so that you don't have to go back and write again.
a source to share