开发者

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

  1. Have a table in the database for each of the different line item options LineItemFreeform, LineItemPriceList, LineItemService etc.

  2. 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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜