I need to model charts such as the one displayed below...

enter image description here

... and came up with the following:

enter image description here

For the sample chart I originally showed..

  • charts.name is "Custom transactions", charts.y_legend is "Amount", x_legend is "Customer Group"
  • series.name is "2007" for position 0 and "2006" for position 1, categories.name is "Foreign" for position 0, "Specialty Stores" for positions 1, etc.
  • chart_values.value will be the value for a given series and category such as $14,000,000 for series "2007" and category "Foreign".

As seen, the unique series can be identified solely on idSeries and position, (and similarly for categories, however, I will no longer be saying so), and thus series.idseries is redundant. Does this violate the 1st, 2nd, 3rd, or Boyce and Codd Normal Form?

Regardless of normalization purity, I am also concerned (yet don't know why) with the implications of using composite key charts_idcharts/position as series primary key. For my application, a backend configuration page will allow the user to add/remove categories and series, rename them, change the position order, etc. To change a series name, the client can provide the chart ID and series position along with the new desired value. To change position, the client could provide an array of the previous positions in a given order [2, 0, 1, 3]. Are there any negative implications of using position in a group in an entities primary key (other than needing to change the primary key upon changing position)?


Which happens more often? Renaming/reordering versus fetching?

If you are fetching most of the time, then make that more efficient (PK order), while letting the other be more costly.

If you are renaming/reordering most of the time, then normalize to simplify that -- at the cost of ordering during fetching.

If the two tasks are the same (or even remotely similar in frequency), the do whichever is easier to code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.