2

Currently I have a system whose front-end looks like this:

The following query:

SELECT      `l`.*, `s`.`month`, `s`.`year`
FROM        `summarylines` `l`
JOIN        `summaries` `s`
ON          `l`.`sumid` = `s`.`id`
ORDER BY    `l`.`sumid` ASC

Produces the following result:

I have a requirement to change the system to handle individual dates, rather than weeks. So on the front-end the columns would be each date, e.g. 2nd Jan, 6th Jan, 10th Jan, 12th Jan, 18th Jan, 20th Jan, 26th Jan, 27th Jan.

It is of course a major overhaul of both the back-end and the DB structure.

My question is: should I use a database with 31 columns for each possible day of the month? Any unused days would be NULL for that field (as it is now for any unused weeks). The positive in this approach is that I only need 1 row per item per month. E.g.

+--------+-------+--------+------+-----+------+------+-----+----+-----+------+-----+
| lineid | sumid | itemid |  1   |  2  |  3   |  4   |  5  | 6  |  7  |  8   | ... |
+--------+-------+--------+------+-----+------+------+-----+----+-----+------+-----+
|   1195 | 15123 |    165 | NULL |  66 | NULL | NULL |  72 | 88 |  95 | NULL | ... |
|   1196 | 15123 |    223 | NULL | 101 | NULL | NULL | 141 | 85 | 110 | NULL | ... |
+--------+-------+--------+------+-----+------+------+-----+----+-----+------+-----+

Or should I have a narrow table, with lots of rows? E.g.

+--------+-------+--------+-----+-----+
| lineid | sumid | itemid | day | qty |
+--------+-------+--------+-----+-----+
|   1195 | 15123 |    165 | 2   | 66  |
|   1196 | 15123 |    165 | 5   | 72  |
|   1197 | 15123 |    165 | 6   | 88  |
|   1198 | 15123 |    165 | 7   | 95  |
|   1199 | 15123 |    165 | ... | ... |
|   1200 | 15123 |    223 | 2   | 101 |
|   1201 | 15123 |    223 | 5   | 141 |
|   1202 | 15123 |    223 | 6   | 85  |
|   1203 | 15123 |    223 | 7   | 110 |
|   1204 | 15123 |    223 | ... | ... |
+--------+-------+--------+-----+-----+

A possible negative of this is that the back-end's SQL is currently coded in such a way that some of the existing code could just be altered if using a 31-col table. It would have to be completely rewritten using this approach.

FYI, most customers only have 1 or 2 deliveries per week, but most have 5-10 different items.

4

It depends on what you are going to do with the data, as it always does. It's usually dangerous to assume that the data is never going to be used in some other way. Your question illustrates this nicely. The original design of "summaries" assumed that weekly summarization would always be the intended use, and now you want daily summarization.

The structure of the summaries table (or view) is what can be called crosstabulation. Crosstabulation is frequently used in spreadsheets as a convenient way of displaying data that has been summarized along two dimensions. See the pivot table feature for more details.

Crosstabulated data is not in first normal form. The folks who invented first normal form were thinking about something when they invented it. They were thinking about keyed access to all data. The power of keyed access to all data is central to the simplicity and power of the relational data model. You can look up more detail under first normal form.

Based on this, I would say that one row per line item is the best structure for this data, assuming that no finer granularity can be obtained. If you want to summarize by day, or by week, or by month or whatever, you can summarize by using a pivot operation in a view.

This will take some computer resources, to be sure. Computer resources are usually cheap, compared to "we can't do what you want because the data isn't organized that way".

As far as existing code goes, just create views that make the data look the way it needs to look for the existing code, and tweak the existing code to use the view. Unless the functionality of the existing code needs to be changed as well.

Edit based on comments:

You probably know more about cross tabulation than you think you do. Look at the diagram you provided at the top of your question. That's a cross tabulation. It tabulates by product down the left side, and by week number across the top. The cells contain some kind of aggregate statistic, like the total count of bath sheets sold in week 2.

It's probably a summary of invoice data, or something like that.

| improve this answer | |
  • Hi Walter, thanks for your answer! It's pretty safe to assume this system isn't going to change drastically again. It is a bespoke browser-based data-entry system and has since been replaced with a bespoke Windows .NET program, which regularly has new features added to it. This particular change is required for the minimal data-entry that is still needed in the legacy system. I'll be honest, the crosstabulation info is going over my head a bit (I'm not primarily a DBA, just a code monkey). I also think 1 row per item would be best, but I don't really know why. – Danny Beckett Jul 9 '15 at 10:32
  • The app will need a total rewrite of the back-end and front-end anyway, since this is a major change. The code is very much geared into using weeks, not days. Moving forward in the new Windows program, views, functions, sprocs and triggers are more heavily used. – Danny Beckett Jul 9 '15 at 10:33
  • 1
    The transition from code monkey to data architect is a rough one. I know, because I made that transition, many years ago. Data that is thrown together by code monkeys nearly always comes back to haunt people who come along later. – Walter Mitty Jul 9 '15 at 10:37
  • If you want to understand crosstabulation, you can start here, en.wiktionary.org/wiki/cross_tabulation and then follow the links. – Walter Mitty Jul 9 '15 at 10:42
  • So far I think this is the best answer; are you able to tell me any other pros & cons of each approach (31-col table vs narrow table)? (or edit your answer). Sorry, it's just a massive change and any additional info would be greatly appreciated! – Danny Beckett Jul 9 '15 at 10:50
1

I think table with lots of rows, in this case, can have several benefit:

  1. You will have columns with a data. Not columns where you give them a meaning.
  2. You can eventually make partitions on this data, depending from your rdbms, with range partition, for example
  3. You can scale aggregate data. For example you can keep existing table as aggregation of new table.
  4. Smarter query for analisy, for example, you can easply query wich day of the month have more of something.

To minimize problems with existing application, you can eventually use functions and views, depending of your rdbms.

| improve this answer | |
  • Thanks for your answer, but I don't understand #1 or #3. I can do #4 with either approach, but this isn't necessary for this application anyway. About minimising problems with the app, it needs a huge rewrite anyway with this change. The RBDMS is MySQL 5. – Danny Beckett Jul 9 '15 at 10:25
  • For 1 I mean that you will have a column with real date value. Now you have columns meaning a value. In my opinion haveing date and datetime datatypes is more elegant, and let you do more complex query. – user_0 Jul 9 '15 at 10:32
  • For 3 I mean you can easly operate on data, but you can also have a finer resolution. Imagine a day you will be requested to have detail by hour (just an example) – user_0 Jul 9 '15 at 10:33
  • Thank you for clarifying! I see what you mean about #1 now. I don't think #3 is applicable here :) Two opposing answers now though! – Danny Beckett Jul 9 '15 at 10:34
1

When modeling the data, consider only the data you have or expect to get:

"We have customers most of whom will have only 1 or 2 deliveries per week with most deliveries having 5-10 different items."

create table Customers(
    ID   int not null auto_increment, -- PK
    ...
);
create table Deliveries(
    ID   int not null auto_increment, -- PK
    CustID int not null,              -- FK
    DeliveryDate date not null,
    ...
);
create table Items(
    ID   int not null auto_increment, -- PK
    DeliveryID int not null,          -- FK
    ...
);

Only when you have the data physically well laid out do you look at the questions that will be asked and what form the answers should be in.

I can't stress this enough: You may have to change the physical design of the schema if the data ever changes. That can't be helped. But you should never have to change the physical design of the schema in response to changes in the questions being asked, new questions being asked or changes in the format of any answers.

In looking over the image of the front end and the associated query, my first thought is to supply a view that aggregates the data into the needed form (weekly by item).

Now when told the data must be in a different form (daily by item), just create a new view for that.

There are, of course, advantages and disadvantages to this method.

The main disadvantage is that the data is aggregated anew with every query. As it happens, this is also an advantage in that there needn't be triggers and/or scheduled tasks to refresh summary table(s) every time the data changes.

Another advantage is that the application developers may, more or less at their leisure, change the app to work with the new data format. As the old data format is unchanged, it continues to be available to fall back to. Development issues may require a full or partial rollback of the app code (easily done), but not the database (not so easily done). At most, you will be asked to tweek the new format. Easily accomplished by rewriting the view.

Also, it is not uncommon to find that just because the app is being changed over here to use the new data format, doesn't mean that some part of the app over there wants to keep using the old format. Now you can keep both happy.

Plus (and I stress this from time to time as the opportunities present themselves) using views decouples the app and db layers which allows you to continually refine and improve the physical layout of the data. Just recreate the views to maintain the expected format and the app people need not even know about the changes.

| improve this answer | |
0

From your explanation, I would assume this is a summary of some other data source / table.

As you are providing this in a browser; response time is the essence. So create a table with your 31 cols. This is not a proper solution but it does work as a fast 'cache' to your web-app.

You do have to ensure that your summary tables are in sync with the actual data source.

You could just as well pass this dataset as a JSON, and display in a manner that you can interact with the dataset (click on a week to expand and show daily values).

Regards,

| improve this answer | |
  • Hi, the word 'summary' is a bit of a misnomer. It's just the name of the table. Historically my client has always called this a 'summary sheet'. It is the true data. As you say, speed is key for the web-app so I agree that using a 31-col table probably makes sense. – Danny Beckett Jul 9 '15 at 10:46

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.