deciding what/how many tables to use; normalisation suggests one

Question

I am using Oracle Database 18c Express Edition with SQL Developer.

I have been asked to build a database to log sales in a shop:

• Date
• Day
• Time
• Value [either Low, Medium, or High]
• Location [on a (-5,-5) to (5,5) grid]

If we throw in an incremental UID as the PK (primary key) for each sale, the one table does this. I would need one entity, Sale, and it would consist of six attributes (the UID and the list above). It would have a PK, no repeating groups, no composite PK, and no non-key fields dependent on another non-key field.

Queries (which might influence the design):

• Find quietest day
• Find busiest hour
• Find highest income location
• Generate a report on sales by value range
• Generate a follow-on report of purchases by hour in each value range
• Identify locations with no income

There are constraints (shop isn't open Sundays, only open between hours x and y, escalator to get to the floor is at (0,0), etc).

Normalisation will do most of the database design, making new tables to separate attributes into their own entities. The task specifies 3NF (and says there needs to be no normalising, confusingly). I am reading "Systems Analysis: A Beginner’s Guide" by Kevin Bowman as my main reference, and the data is already in 3NF, and doesn't need more than the one table.

This seems too simple, and I suspect I am not getting the process. A colleague plans on a table for each attribute, which seems mad and unnecessary.

What are the issues with my reasoning?

Answer 1

One point you might consider is to store the actual sales value any way. If you store ranges a user must convert from value to range and might make errors. If you create a table storing the ranges, the system can do this task. You will also be able to changes ranges later.

Normalisation also means not storing redundant information. As you can derive the day from a date (with Format) you do not need both. It might also cause inconsistent data.

Last thing you could do is to create a location table. Finding locations without sales is hard if the system does not know which locations exist. It will also help validate the user input.

Answer 2

There must be a rule for value range from value, which implies you’re losing data: what if the bands are revised later?

Better to store the detail (sale cost) & model the banding separately.

Day has already been mentioned (wouldn’t be 3nf with both, see your texts)

I would also model location and represent here with an FK rather than include it.

Consider what it will mean to reorganise the shop floor based on previous sales data (Physical coordinates are but one component in location)

Which locations are by the till, by the door etc: place the coordinates in the sale table & it is difficult to accommodate new attributes readily.

Answer 3

This probably should have been a simple comment, but as I am a new user I'm not allowed to post those. I'll try to flesh out the answer a bit.

You statement is correct for the requirements you've set: your single table would be sufficient. However, you seem to be modeling with the questions you already know will be asked in mind. You are ignoring the possibility that additional questions will need answering in the future.

This explains why XPS35 states you may be losing data (which, in fact, you are). To remain flexible in the questions your data may answer, you may want to consider storing the data with the highest granularity you can think of (or at least deem relevant). As such, I'd suggest storing transactions by explicit date/time, allowing you to group on a higher granularity later.

For instance, I see no reason to sacrifice the significance of [Value] in this model. You may just as well store the actual value, which would allow you to determine averages per location later on. In your current proposal there is no way to calculate the average, as you only know 'Low', 'Medium' or 'High'.

This will also allow you to change the definition of these terms later on. You can always 'map' the [Value] to ranges stored in a separate table (as suggested by XPS35) when reporting/analyzing your data.

Answer 4

Normalization is good in an operational database, and bad in an analytic database. Normalization helps prevent update anomalies, but makes analysis harder. Generally speaking, for analysis, you want a dimensional model, which is flatter and allows for duplication