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?
Best Answer
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.