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;
- 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?