The Data Model Resource Book

Len Silverston

Mentioned 21

A quick and reliable way to build proven databases for core business functions Industry experts raved about The Data Model Resource Book when it was first published in March 1997 because it provided a simple, cost-effective way to design databases for core business functions. Len Silverston has now revised and updated the hugely successful First Edition, while adding a companion volume to take care of more specific requirements of different businesses. Each volume is accompanied by a CD-ROM, which is sold separately. Each CD-ROM provides powerful design templates discussed in the books in a ready-to-use electronic format, allowing companies and individuals to develop the databases they need at a fraction of the cost and a third of the time it would take to build them from scratch. Updating the data models from the First Edition CD-ROM, this resource allows database developers to quickly load a core set of data models and customize them to support a wide range of business functions.

More on Amazon.com

Mentioned in questions and answers.

This is a question not really about "programming" (is not specific to any language or database), but more of design and architecture. It's also a question of the type "What the best way to do X". I hope does no cause to much "religious" controversy.

In the past I have developed systems that in one way or another, keep some form of inventory of items (not relevant what items). Some using languages/DB's that do not support transactions. In those cases I opted not to save item quantity on hand in a field in the item record. Instead the quantity on hand is calculated totaling inventory received - total of inventory sold. This has resulted in almost no discrepancies in inventory because of software. The tables are properly indexed and the performance is good. There is a archiving process in case the amount of record start to affect performance.

Now, few years ago I started working in this company, and I inherited a system that tracks inventory. But the quantity is saved in a field. When an entry is registered, the quantity received is added to the quantity field for the item. When an item is sold, the quantity is subtracted. This has resulted in discrepancies. In my opinion this is not the right approach, but the previous programmers here swear by it.

I would like to know if there is a consensus on what's the right way is to design such system. Also what resources are available, printed or online, to seek guidance on this.

Thanks

Are there any good resources (books, authoritative guides, etc.) for design patterns or other best practices for software that includes financial accounting features?

Specifically, where is good information about handling issues like the following:

  • Internal representations of money quantities
  • Internal representations of accounts, journals, and other records
  • Reconciling inconsistencies (either automatically or via user action)
  • Handling ends of accounting periods (daily, weekly, monthly)
  • Designing UIs and printed financial reports that make sense to businesspeople

Note: "Authoritative" or otherwise widely-accepted information is what we're looking for here. Otherwise, this will just turns into a big list of anecdotes of all the things people have tried, making the topic very subjective.

I find the Data Model Resource book to be a good source of inspiration for modeling business structures. Apache Ofbiz ERP was built around the concepts in this book.

Martin Fowler's Analysis Patterns covers some of those topics.

There's a lot of sites out there that teach people how to build better software--but why is it that there are very few sites that actually give detailed descriptions of the domains that we (as programmers) are supposed to create? One can only construct so many inventory, accounting, and ERP systems before a pattern of common requirements start to emerge among the different types of systems. Logically speaking, if programmers spend so much time trying to create reusable components in their architectures, does that imply that they should have to have some reusable "blueprint" that describes the systems that they're supposed to create? In other words, it seems like the focus of software development has been too focused on "how" software should be built rather than to catalog and accurately specify (with detailed requirements) "what" should be used in the first place.

So my question is this: Has there been any work done to catalog all the different types of system specifications into a single place, all on a single site? If lacking the proper requirements at the start of the project is one of the banes of software development, wouldn't it make more sense to be able to 'reuse' requirement specifications from previous systems of the same type that have already been written?

Check out the Data Model Resource Book by Len Silverston:

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1232336996&sr=8-1

It approaches reusable design from the data model point of view, as opposed to end user requirements or OO designs. However, I find that to be very useful - once you have a good grasp of the data model, you have a big jump on the requirements and the entities that will eventually be modeled as classes.

You might want to check out Martin Fowler's Patterns of Enterprise Application Architecture - while not specs, it seems to be about the sort of things you are after.

Disclaimer: I haven't read it myself, I only know of its existence.

Here's a software design question I've encountered several times and have never found an ideal solution for (I'm also dealing with it now again.)

Many applications need some form of user/role management. You have base users, groups that these users can belong to (not limited to just one), roles and permissions they have, organizational units, and a whole bunch of properties and other features that are project-specific.

My question is, what ways do people know of and/or have experience with to design and build a really dynamic, flexible user management system? Are there any design patterns you know of that really help?

Chapter 2 of The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises might be helpful.

I have been given the task to design a database to store a lot of information for our company. Because the task is rather big and contains multiple modules where users should be able to do stuff, I'm worried about designing a good data model for this. I just don't want to end up with a badly designed database.

I want to have some decent examples of database structures for contracts / billing / orders etc to combine those in one nice relational database. Are there any resources out there that can help me with some examples regarding this?

The Data Model Resource Book.

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=dp_cp_ob_b_title_0

HEAVY stuff, but very well through out. 3 volumes all in all...

Has a lot of very well through out generic structures - but they are NOT easy, as they cover everything ;) Always a good starting point, though.

As a parallel to my question about Useful stock SQL datasets, I wonder if anyone has come across a library/web page/wiki/etc. that contains stock SQL data models. For example, the problem of maintaining table(s) for login credentials is faced by countless developers. Are any of you aware of a data model that provides a best-practices implementation of this table? What about other use cases? I'm thinking about things like inventory control, sales reporting, application events (login, logout, viewing a given page or item, etc.). I came across a great resource at databaseanswers.org, but wonder if there are any other resources I should take a look at.

As with my question on data sets, I'm looking for things that can readily incorporated into an application, fulfill a generic need that many developers have, and something that is not specific to a single industry or business sector. Address books, login credential tables, messaging, etc. all fall into the criteria I'm thinking about.

Any thoughts?

Have you seen the SO question relational-database-design-patterns?

EDIT: Having got interested in this question I had a look for any books on the subject and found the following volumes:

The Data Model Resource Book: A Library of Universal Data Models for All Enterprises: Vol 1

The Data Model Resource Book: A Library of Universal Data Models by Industry Types: Vol 2

The Data Model Resource Book: Universal Patterns for Data Modeling Vol 3

I've not read the series, so I can't recommend them myself, but they look interesting.

I have medium sized MySQL database with a primary "persons" table which contains basic contact information about every human being connected to the theatre and theatre school for which I am responsible for maintaining and developing a number of web applications.

Some persons are just contacts - that is, their "persons" table record is all the information we need to store about them. Many others though have to be able to assume different roles for a variety of systems. Of these, most start out as students. Some start as employees. People who are students can become interns or performers; employees can become students; all teachers are employees and performers, etc.

In essence, their are a variety of different "hats" that any individual person may have to wear in order to access and interact with different parts of the system, as well as have information about them made available on public pages on our site.

My choice for implementing this model is to have several other tables which represent these "hats" - tables which contain meta-information to supplement the basic "person" info, all of which use the "persons" id as their primary key. For example, a person who is a teacher has a record in a teachers table containing his or her short biographical information and pay rate. All teachers are also employees (but not all employees are teachers), meaning they have a record in the employees table which allows them to submit their hours into our payroll system.

My question is, what are the drawbacks to implementing the model as such? The only other option I can think of is to inflate the persons table with fields that will be empty and useless for most entries and then have a cumbersome table of "groups" to which persons can belong, and then to have almost every table for every system have a person person_id foreign key and then depend on business logic to verify that the person_id referenced belongs to the appropriate group; But that's stupid, isn't it?

A few example table declarations follow below, which hopefully should demonstrate how I'm currently putting all this together, and hopefully show why I think it is a more sensible way to model the reality of the various situations the systems have to deal with.

Any and all suggestions and comments are welcome. I appreciate your time.

EDIT A few respondents have mentioned using ACLs for security - I did not mention in my original question that I am in fact using a separate ACL package for fine-grained access control for actual users of the different systems. My question is more about the best practices for storing metadata about people in the database schema.

CREATE TABLE persons (
    `id`            int(11) NOT NULL auto_increment,
    `firstName`     varchar(50) NOT NULL,
    `middleName`    varchar(50) NOT NULL default '',
    `lastName`      varchar(75) NOT NULL,
    `email`         varchar(100) NOT NULL default '',
    `address`       varchar(255) NOT NULL default '',
    `address2`      varchar(255) NOT NULL default '',
    `city`          varchar(75) NOT NULL default '',
    `state`         varchar(75) NOT NULL default '',
    `zip`           varchar(10) NOT NULL default '',
    `country`       varchar(75) NOT NULL default '',
    `phone`         varchar(30) NOT NULL default '',
    `phone2`        varchar(30) NOT NULL default '',
    `notes`         text NOT NULL default '',
    `birthdate`     date NOT NULL default '0000-00-00',
    `created`       datetime NOT NULL default '0000-00-00 00:00',
    `updated`       timestamp NOT NULL,
    PRIMARY KEY (`id`),
    KEY `lastName` (`lastName`),
    KEY `email` (`email`)
) ENGINE=InnoDB;

CREATE TABLE teachers (
    `person_id`     int(11) NOT NULL,
    `bio`           text NOT NULL default '',
    `image`         varchar(150) NOT NULL default '',
    `payRate`       float(5,2) NOT NULL,
    `active`        boolean NOT NULL default 0,
    PRIMARY KEY (`person_id`),
    FOREIGN KEY(`person_id`) REFERENCES `persons` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE classes (
    `id`            int(11) NOT NULL auto_increment,
    `teacher_id`    int(11) default NULL,
    `classstatus_id` int(11) NOT NULL default 0,
    `description`   text NOT NULL default '',
    `capacity`      tinyint NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`teacher_id`) REFERENCES `teachers` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(`classstatus_id`) REFERENCES `classstatuses` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    KEY (`teacher_id`,`level_id`),
    KEY (`teacher_id`,`classstatus_id`)
) ENGINE=InnoDB;

CREATE TABLE students (
    `person_id`     int(11) NOT NULL,
    `image`         varchar(150) NOT NULL default '',
    `note`          varchar(255) NOT NULL default '',
    PRIMARY KEY (`person_id`),
    FOREIGN KEY(`person_id`) REFERENCES `persons` (`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE enrollment (
    `id`                int(11) NOT NULL auto_increment,
    `class_id`          int(11) NOT NULL,
    `student_id`        int(11) NOT NULL,
    `enrollmenttype_id` int(11) NOT NULL,
    `created`           datetime NOT NULL default '0000-00-00 00:00',
    `modified`          timestamp NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`class_id`) REFERENCES `classes` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(`student_id`) REFERENCES `students` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(`enrollmenttype_id`) REFERENCES `enrollmenttypes` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

I went through a similar thing last year. There the question was: do we model our entities explicitly or generically? In your example, that would mean having entities/tables like teacher, student, etc with direct relationships between them or not.

In the end we went for a generic "Party" model. The Party model is as follows:

  • A Party represents a person or organisation;
  • Most Party types had a dependent table to store extra information depending on the party type eg Person, Organization, Company;
  • Things like Student or Teacher are Party Roles. A Party may have any number of Party Roles. A Person may be both a Teacher and a Student, for example;
  • Things like classes are handled as Party Role Relationships. For example, a relationship between a Teacher and Student role indicates a class relationship;
  • Party Role Relationships can have subtypes for extra information. A Teacher-Student Relationship in your model is an Enrolment and that could have the extra attributes you're talking about;
  • Parties don't have direct relationships with each other. Only Party Roles relate to each other; and
  • For common groupings of information, we created views if it helped because the SQL can be a bit convoluted as the relationships are more indirect (eg there are three tables in between the Party entities for a Teacher and Student).

It's an extremely powerful model, one that is pretty common in CRM type systems. This model pretty much came from "The Data Model Resource Book: Volume 1", which is an excellent resource for such things.

I'm looking for some architecture ideas on a problem at work that I may have to solve.

the problem.
1) our enterprise LDAP has become a "contact master" filled with years of stale data and unused and unmaintained attributes.
2) management has decided that LDAP will no longer serve as a company phone book. it is for authorization purposes only.
3) the company has contact type data about people in hundreds of different sources. we need to scrub all the junk out of LDAP and give the other applications a central repo to store all this data about a person.

the ideal goal
1) have a single source to store all the various attributes about a person
2) the company probably has info on 500k people ( read 500K rows)
3) i estimate there could be 500 to 1000 optional attributes on these people. (read 500+ columns)
4) data would primarily be set/get via xml over jms (this infrastructure is already in place)
5) individual groups within the company could "own" columns. only they would be allowed to write to their columns, they would be responsible for keeping the data clean.
6) a single record lookup should be returned in sub seconds
7) system should support 1 million requests per hour at peak.
8) the primary goal is to serve real time data to the enterprise, reporting is a secondary goal.
9) we are a java, oracle, terradata shop. we are your typical big IT shop.

my thoughts:
1) originally i thought LDAP might work, but it doesn't scale when new columns are added.
2) my next thought was some kind of no-sql solution, but from what i have read, I don't think i cant get the performance I need, and its still relatively new. I'm not sure i can get my manager to sign off on something like that for such a critical project.
3) i think there will be a meta-data component to the solution that will track who owns the columns and what each column represents, and the original source system.

Thanks for reading, and thanks in advance for any thoughts.

You may want to look into Len Silverston's Party Model. Here's a link to his book: http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237.

I have no experience building something on that scale, though I think that thinking of it as 500k rows x 500 - 1000 columns sounds a bit ridiculous.

I found http://www.databaseanswers.org/data_models/ very useful. Any other suggestions?

I'm attempting to create a contacts application that has two main entities - person and company. A person can have many emails, numbers, and addresses. A company can also have many emails, numbers, and addresses. I'm trying to determine the proper design for this scenario.

Option #1 - multiple foreign keys
Emails, numbers, and addresses will have two columns called person_id and company_id. Depending on which entity the data belongs to, one will be null and the other will contain an id linking back to the parent.

Option #2 - one table per type per entity
I duplicate each table so there would be a company_addresses table and a person_addresses table. I would have twice as many tables, but this is the solution that makes the most sense right now.

Option #3 - one link table
I create one table - "link". This table will contain four columns: source_id, source_entity, dest_id, dest_entity. So if a company gets a new number you would have a row like: 1, number, 2, company.

Option #4 - multiple link tables
I create a table for each type of link (company_address, person_address, company_email, person_email, etc.)

Which option would you choose?

You've touched on a couple of practices I would argue that you avoid. I wrote more about this in Database Development Mistakes Made by AppDevelopers (eg exclusive arcs).

As to your problem, I would actually choose none of those options. What you're stumbling towards is a generic Party model. Basically you have a Party entity with subtypes such as Person and Organisation. Contacts has a Party ID as a foreign key. The use of a common superclass/superentity is much more profound than that however as you will find that you use this for many other things as well (eg the whole role concept).

A lot of these database design modelling problems have mature solutions to them but it doesn't tend to be the sort of thing programmers are ever taught. I highly recommend getting the book The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises, which goes into much more detail about how to model people and organisations as well as many other typical problems.

The key point to remember here is that what you're doing has been done before.

I want to study an ERP system. For that I want to study its processes. So if anyone can give me some useful links then it will be helpful to me, thanks.

Check out The Data Model Resource Book series from Silverstron. Not UML... but close. Volume 1, volume 2.

I'm looking for a resource that shows common design patterns or best practices for data models. For instance, a [very simplified] course model might have:

Student has many Enrollments

Course has many Sections

Sections has many Enrollments

There seem to be many approaches to setting up data models and relationships, and some are more obvious than others. Often, I find myself thinking about it one way and finding out much later that there is a quirk or complication I missed.

For more complex systems, like ERP, Reservations, eCommerce, etc., there must be some standards and best practices but I can't find them anywhere. Any resource suggestions would be welcome. I've googled around quite a bit, but I didn't see any that made sense.

This is a copy of my answer on DBA.

David Hay's

Len Silverston's

Michael Blaha's Patterns of Data Modeling. This one has some interesting temporal, graph, and tree models.

Martin Fowler's Analysis Patterns. This one skims some of the other patterns, but gives accounting a solid treatment.

They are all well-rated, and I have read all but one, and they are all very good. Several of them are available on safaribooksonline.

Also, OASIS's Universal Business Language, schemas

I am developing a website for a manufacturing company. The company has a few thousand part numbers. The parts fall into around 150 categories. Each product category has specifications (length, height, width, etc).

I need to find a way to store all of this in my database for the website. The product specifications have me completely stumped. Each category has a different number of specifications ranging from 5 to over 50 (they are complex engineering products). I need to find a way to put them into my database so that I can get them out in table format, edit the values in a web page, search them on my website, and export them into Print and Catalog formats.

I have looked into Master Data Management and Product Information Management Systems but they all seem very cost prohibitive for a company our size. Does anyone know of a software package or an easy way to write their own that will solve this issue?

My initial attempts have involved loading the data into 3 tables. A PropertyGroup table, a Property table (each property belongs to 1 group), and a ProductProperty table (each ProductProperty has a Product_ID and a Property_ID). My 2 concerns with this format is getting the data out in a dynamic and pivoted format and some of my property groups have levels of headers. By levels of headers I mean for example 3 properties could have their own header and also share a second header above their single column one. I also looked at putting all of the data into excel but that seems to lose some of the data granularity that I was looking for.

Any ideas?

Check out:

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=dp_ob_title_bk

and the volumes 2 and 3. I am sure you will find a full schema for your problem there ;)

My question is: do you know of any real good reference (book, web) for ERP database design ?

I have built an ERP using Sql Server as a back-end. There are different types of Client documents (there are also Supplier Docs):

  1. Order -- impact: Back-Orders (BO)
  2. Delivery Note (also used for returns, with negative quantity) --impact: BO, Stock
  3. Invoice --impact: accounting only
  4. Credit Note --impact: accounting, BO

BO tracking is very complex, and involves tracking links between different documents: imagine a returning customer that has ordered several times 10 units of reference X: which delivery fills which order ? If there is a credit note for 5 units of reference X, does it have an impact on BO ? For which order ? (these are just examples)

The question is more simple than what it looks. There are many use cases that are well known and people have put a lot of thought into them. For example: Audit trailing, login users, and so on. We are looking for a good resource site that present the DB design for those common use cases.

Try The Data Model Resource Book by Len Silverston. It has 3 volumes and not only shows but also explains the usual use cases.

Hello I have the following mysql relationship,

enter image description here

As you can see the companies table has a 1:n relationship withe the members table. However this seems to implicit, meaning that when I try and save a member to my database they have to be associated with a company, or they insert does not happen.

Basically the architecture of our user signup process is that you can be an individual or sign as a company. How do I make my database design reflect this, so the relationship between the two tables is not a must.

This is not a great design.

You should read up on the Party Model, and Table Inheritance to see a design built to last. A skeleton:

PARTY
id

ORGANIZATION : PARTY
name
doing_business_as

INDIVIDUAL : PARTY
first_name
last_name

PARTY_RELATIONSHIP
from_party_id references party(id)
to_party_id references party(id)
from_date
to_date

ORGANIZATION_CONTACT_RELATIONSHIP : PARTY_RELATIONSHIP

Here's my problem: I'm about to design and implement quite a big sales module on top of an existing business (web) application. What I need is basically this - a complete e-commerce layer without the public part of it (I don't need a cart, payment gateways etc.). I'm going to build this thing from scratch since I'm not willing to hack any of the existing solutions (I'm using Rails, just to complete the picture). I've been thinking about the whole thing for a while and checked some of the existing solutions for inspiration about how to kick-off my data modeling. I'm pretty far with the model now, however, I can't resist to have a feeling that (at least to some extent) I'm reinventing the wheel here. I mean, there have to be thousands of data models out there which are similar (if not identical) to mine. I tried to find some of them but with no great success. What I want is not a complete database model, just a logical model that I can use as a checklist when doing my own thing. What I found so far is just this site which is more of a collection of database models without any description or reasons behind the models at all.

So, my question is: Do you know of any sites where to look for a data modeling inspiration?

EDIT: Since none of the answers so far provided me with what I am looking for, I'll try to refine my question. I'm not looking for any particular database diagrams (and as I mentioned I'm not interested in reverse-engineering of other existing systems), nor do I need to study the subject of data modeling in general. What I'm looking for is something like an online version of this book. It's just hard to believe there's nothing like that out there.

Thank you all, anyway.

I have a DB schema from The Data Model Resource Book, Vol. 1. In it is a table like this:

CREATE TABLE [dbo].[AccountingPeriod](
  [AccountingPeriodID] [int] NOT NULL,
  [RoleTypeID] [int] NOT NULL,
  [PeriodTypeID] [int] NOT NULL,
  [AcctgPeriodNum] [int] NOT NULL,
  [FromDate] [smalldatetime] NOT NULL,
  [ThruDate] [smalldatetime] NOT NULL,
  [PartyID] [int] NOT NULL,
PRIMARY KEY CLUSTERED (
  [AccountingPeriodID] ASC)

With a constraint defined as:

ALTER TABLE [dbo].[AccountingPeriod]  WITH CHECK ADD FOREIGN KEY([AccountingPeriodID])
REFERENCES [dbo].[AccountingPeriod] ([AccountingPeriodID])

The AccountingPeriodID column has a self referencing foreign key that the text claims is a recursive reference column but I think it's an error. I think I need another column to properly store recursive references in this table. Can the author's method be implemented with the table definition supplied, why?

Yes, You need another column which will reference the primary key column of the table itself, for self referencing a table. Self referencing column really does not make any sense.

I'm working on a new point of sale for a company that products for different prices depending on the product mix.

All products have a base price.

To explain my problem, I will use the following info:

Product         Category        Price
A               1               45
B               1               70
Q               2               20
R               2               27
S               2               15
X               3               17
Y               3               22
Z               3               16

The company has Packages, for example Package "Combo": for product A or B, if you choose 1 of Q or R and 1 of X, Y or Z you get a $20 discount.

Case A: Sometimes customers add on to a base product when placing an order, for example: They wan't one of Product A, and they add Product Q and Product P to that to create a package that has a discounted price. Then they might add that they want 1 of product B with 1 R and 1 Z.

Case B: Sometimes customers will add 1 A and 2 B, 2 Q, 1 S, 2 X and 1 Z. According to the rules set forth by "Combo" Package, only 2 combos would apply because S is not a combo item.

Other promotions are dependent on quantity, so if you buy 2 of B you get 20% off and/or dependent upon time, it is only valid after 5 PM or before 10% off if before 10am. Another promotion might depend on when your last purchase occured or if you have purchase more than $X in Y timeframe.

My Problems:

1) How do I structure the tables so I my create the different packages or promotions in a way that is very flexible to add different types of promotions with different requirements?

2) When they order like Case B (or a mix of Case A and Case B) how do I structure my query so that I can test to see what product mix(es) are in the order, and update the prices/descriptions accordingly? Ultimately, the best result for this query would return which packages and promotions have requirements fulfilled in order of which gives the most benefit to the customer (i.e. maybe what they ordered fulfills requirements for promotion 1 and 3, but promotion 3 is less expensive. This must work with multiple promotions).

Thanks in advance for the help!

UPDATE #1

To better describe the problems at hand and to update the work done thus far to solve them I'm incluiding an ERD of the Product Model limited to the entities and attributes that affect the issue (i.e. inventory is not at play here, so no inventory entities are present).

I'm also including sample data from the entities and attributes that affect this question (to simplify reading the data, I'm putting in name/descriptions in place of Foreign Keys):

Here is a link to a flow chart giving an example of a combo, a fast and visual way to understand the table structure.

PRODUCT
---------
ID  Name
================================
1   Hamburger
2   Cheeseburger
3   Bacon Hamburger
4   Bacon Cheeseburger
5   Orange Juice
6   Apple Juice
7   Coffee
8   Coke
9   French Fries
10  Onion Rings
11  Soup du Jour
12  Hamburger Combo
13  CheeseBurger Combo
14  Bacon Hamburger Combo
15  Bacon Cheeseburger Combo
16  Combo Side
17  Combo Beverage
18  Small Orange Juice
19  Large Orange Juice
20  Small Apple Juice
21  Large Apple Juice
22  Add Extra Patty
23  Add Avocado

PRODUCT COMPONENT
------------------
productFrom                 productTo       
===================================================
Hamburger Combo             Hamburger
Hamburger Combo             Combo Side
Hamburger Combo             Combo Beverage
CheeseBurger Combo          Cheeseburger
CheeseBurger Combo          Combo Side
CheeseBurger Combo          Combo Beverage
Bacon Hamburger Combo       Bacon Hamburger
Bacon Hamburger Combo       Combo Side
Bacon Hamburger Combo       Combo Beverage
Bacon Cheeseburger Combo    Bacon Cheeseburger
Bacon Cheeseburger Combo    Combo Side
Bacon Cheeseburger Combo    Combo Beverage

PRODUCT FEATURE
----------------
ID  Description
=======================
1   Combo Side Option
2   Combo Beverage
3   Juice
4   Orange Juice Size
5   Apple Juice Size
6   Extras

PRODUCT FEATURE APPLICABILITY
------------------------------
product                     productFeature  ProductFeatureApplicabilityType
============================================================================
Hamburger Combo             Combo Side      Required
Hamburger Combo             Juice Flavor    Required
Cheeseburger Combo          Combo Side      Required
Cheeseburger Combo          Juice Flavor    Required
Bacon Hamburger Combo       Combo Side      Required
Bacon Hamburger Combo       Juice Flavor    Required
Bacon Cheeseburger Combo    Combo Side      Required
Bacon Cheeseburger Combo    Juice Flavor    Required


PRODUCT FEATURE APPLICABILITY
------------------------------
productFeature  product             ProductFeatureApplicabilityType
============================================================================
Combo Side      Hamburger Combo             Required
Combo Beverage  Hamburger Combo             Required
Extras          Hamburger Combo             Optional
Combo Side      Cheeseburger Combo          Required
Combo Beverage  Cheeseburger Combo          Required
Extras          Cheeseburger Combo          Optional
Combo Side      Bacon Hamburger Combo       Required
Combo Beverage  Bacon Hamburger Combo       Required
Extras          Bacon Hamburger Combo       Optional
Combo Side      Bacon Cheeseburger Combo    Required
Combo Beverage  Bacon Cheeseburger Combo    Required
Extras          Bacon Cheeseburger Combo    Optional




OPTIONAL FEATURE
------------------
productFeatureFrom  Product             ProductFeatureTo        
=============================================================
Combo Side Option   French Fries
Combo Side Option   Onion Rings
Combo Side Option   Soup du Jour
Combo Beverage                          Juice
Combo Beverage      Coffee
Juice                                   Orange Juice Size
Juice                                   Apple Juice Size
Orange Juice Size   Orange Apple Juice
Orange Juice Size   Orange Apple Juice
Apple Juice Size    Small Apple Juice
Apple Juice Size    Large Apple Juice
Extras              Add Extra Patty
Extras              Add Avocado

So, with the research and support provided by the community up until know, I have been able to solve Problem #1. In fact, I have done so with more flexibility than I thought I could do in the first deployment of the system.

All though there have been advances with Problem 2, it is not solved to satisfaction. There have been some ideas on how to do this, Neil McGuilgan asked a great question leading towards a possible solution using Relational Division and this book helped a lot. However this solution at present, and as I understand it, only works with "one" record (combo) at a time. If a customer Walks up and says he wants 2 cheeseburgers, 1 hamburger, 1 small Apple Juice, 1 Coke, 1 French Fries and 2 onion Rings, I need a way to detect that there is only one combo in the mix and add the other products at base price. If there are multiple combo combinations, I'm looking (ideally for an sql query) that can prioritize combo combinations by largest savings.

One idea I've come up with to solve problem two is to add and attribute to PRODUCT COMPONENT flaggin the main product for the combo (i.e. hamburger). Then when running the pricing process, query what products in the order are main products in a "package", relating the query to the discount given by the PRICE COMPONENT table and ordering by that value (descending), and in that order of packages check to see if you can create a "package" with the remaining non-main products with a query and loop the process until there are no more main products or there are no more non-main products in the remainder.

I've got the following scenario:

In an order tracking system where sales reps track their orders: Each order can have a total of 4 product types(commissionable buckets) and each bucket can 1 or none products of that product type.

So at max an order can have 4 products(one of each of the possible types)

The information I need to track for each of the four product types is the same: product-sold-id(fk), status, product-given-id(fk)

What would be the advantages of adding 12 columns to the order table: 3 for each product type like product-type1-sold-id, product-type1-status, product-type1-given-id..... Etc?

This would be easiest but my issue with this is that most orders would only have 2 or 3 product types sold which would result in 3 null fields per product type not sold. With 100 reps @ 10 orders per day this would result in a lot of null fields.

I am leaning toward have an order items table with an order-id(fk) to cut down on all the null fields. The issue with this approach would be that for one, I would have to add a level of complexity to either my client code to handle nested data, or my server code would have to flatten the data when requested and also be able to parse and save the flat data when sent modified from the client. Also with 100 reps@10 orders per day with 1-4 product types per order this would result in 1000-4000 rows added to the order items table per day. Is there any way of overcoming this?

Any advice would be appreciated. I am using extjs clientside, php serverside, with MySQL as my db.

Build a standard order data model, and put your application logic where it belongs, i.e. in the application layer (or maybe db sprocs).

What if your order maximum of 4 changes?

Maybe stackoverflow can release theirs?

:)

Try The Data Model Resource Book by Len Silverston. It has 3 volumes and not only shows but also explains the usual use cases for ER diagrams.