0

More of a conceptual question for the community, as I am not "thinking with databases" as much as I would like to just yet. 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. The things to log are;

  • Date
  • Day
  • Time
  • Value [grouped, either Low, Medium, or High, rather than absolute monetary value]
  • Location [on a (-5,-5) to (5,5) grid]

If we throw in an incremental UID as the Primary Key for each sale, I can't see why I would need any more than the one table to do this. I need one entity, Sale, and it would consist of six attributes (the UID and the list above). It would have a primary key, no repeating groups, no composite PK, and no non-key fields dependent on another non-key field.

Queries I have been asked to generate (which I don't need help with, yet, but might influence the design) are;

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

There are obviously some constraints I need to deal with (shop isn't open Sundays, only open between x and y hours, escalator to get to the floor is at (0,0), etc), but these are relatively simple and I am confident that some more reading and tweaking will get me there. Technical problems are much easier to work though, in my experience.

My real drama comes from trying to come up with the database design. Normally I would expect normalisation to do most of the grunt work for me, directing me to where I need to make new tables to separate off different attributes in to their own entities. The task specifies data needs to be 3NF (and then also goes on to say there needs to be no normalising, confusingly). I am reading through "Systems Analysis : A Beginner’s Guide" by Kevin Bowman as my main point of reference, and based on my understanding of it, the data I am expecting to log is already 3NF, and doesn't need more than the one table.

This seems an awful lot too simple, and I suspect I am simply not getting it at some point in the process. A colleague has said he plans on making a table for each attribute, which just seems mad and unnecessary to me.

Does anyone have any thoughts, or see any issues with my reasoning?

  • Schema/Database design is an iterative process - check out the articles here on the topic of questions about schema design on the forum - they are normally too broad fit well into the scope of this site. Design your first schema, experiment with it - find the bugs (there will be bugs), fix those - then rinse and repeat. If you have more specific questions, then come back to us here with those. p.s. welcome to the forum and best of luck with your project! :-) – Vérace Dec 31 '19 at 17:54
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.

0

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.

0

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.

0

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

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.