Multiple Tables or Field With XML Data Type?
I’m looking for a little advice on database/model design. I’m setting up a quote system and was wondering how to design the line items table(s) as there are multiple ways of specifying a single LineItem
;
- Freeform: Just enter description and total
- Price List Lookup: Select an item and a qty from a price list
- Service by Time: Select a cost centre and number of hours
- Bought Out Goods: Select best price and add mark-up
- etc.
I have a Quote
table that holds the general data (Quote number, client, user date created/modified etc.), the Quote
can have many QuoteSection
, QuoteSection
can have many LineItem
.
I can see two (reasonable?) options at the moment
Have a table in the database for each of the different line item options
LineItemFreeform
,LineItemPriceList
,LineItemService
etc.Have a generic LineItem table with an XML Data field, each LineItem type I add has a different XML开发者_JAVA技巧 schema
Which of the two (if either!) is the better/most flexible option and are there any other suggestions for modelling this type of data?
Not a lot to go on there, but I'd think about a "line item" supertype, and subtypes for each kind of line item.
I didn't read it closely, but this looks like a fairly decent description of supertype/subtype modeling.
精彩评论