Help with relations in a database design

I have a database design I'm working on that's a retail cash register.  I believe I can tell from looking at what I've done that it's probably not right and violates who knows how many guidelines for relationships.  I was wondering if someone can tell me what I'm doing wrong.

Organization (the owning entity)
Location (physical location of the retail store)
Period (the accounting period in question - usually a single day)
Register (the physical register that sits on the counter)
Drawer (the cash drawer inserted into the register at the start of a shift - a new one for each shift and "owned" by one employee)
DrawerTransaction (the entry that represents a single transaction, including sale and tender of money)

As you can see from the diagram, Organizations contain one or more locations and a location has one or more registers and a register has one or more drawers.

The question I have is how to handle the "Periods" table.  Periods are either open or closed, where a closed period prevents any adjustments to any transactions and provides a reporting point for the day's business.  That means that drawers belong to periods, making periods a defining entity for a drawer.  At the same time, a register is the physical device in which the drawer goes (and cannot be moved to another register) so it also is a defining entity.

What have I done wrong.  This plain just doesn't look right.

 The current database layout from MSSQL 2008
Who is Participating?

Improve company productivity with a Business Account.Sign Up

danrosenthalConnect With a Mentor Commented:
I don't think there is anything "wrong" with your layout.

This is what Period should look like:
Period: id(PK)    locationId(FK)

Also, I am guesssing that you might want an Employee table that links between Organization and Drawer in much the same way that Period links between Location and Drawer.

So Drawer will be an instance of a register and period (and possibly also employee)

Looks like it would work to me.

You could make periods link directly to Organizations or eliminate periods completely and use a simple start and end date in the Drawers table, but it all depends on your requirements.
nhwillyAuthor Commented:
Thanks for the reply.

The Period row would contain data unique to the period.  For example, the status of the period.  

Most importantly, I need the period's transactions to stay open for review and editing by a manager but unavailable to add new transactions to.  When the manager is finished reviewing the period, he/she closes it.  While that's going on I need to have the next period open so the registers continue to process transactions.  This same process happens for drawers.  Swap one out and get the next one in the register and processing transactions while the first clerk balances his/her drawer.

It just looks like normal form would prevent this.  It seems like Drawers is inheriting Locations and Organizations from both Registers and Periods and that seems fishy.

If I hook periods to the organization, I force all locations to synchronise the close of their business day - which doesn't work if they're in different time zones or simply have different hours.  I think that's an OK compromise for a single location.

In the end the reporting is probably best served with a single set of numbers for a calendar day, and if I want operations to match accounting, I have to make these move forward in lock step.

It's almost like I'm missing something really obvious.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<What have I done wrong.  This plain just doesn't look right.


> You have not clarified a functional dependency and cardinality between DRAWER and REGISTER.  If the cardinality is M:N then you must have an associative entity called DRAWER_REGISTER between the two entities with a date timestamp.
> Once you do the above, simply create a REGISTER_TRANSACTION table with a foreign key pointing to REGISTER.

That should help...
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Below should help...

PK: natural key
SK: surrogate key
FK: foreign Key
NN: not null
GD: Getdate default

Organisation: name(PK), id(SK)
Location: name(PK), organisation (FK--> organisation id)
Register: id(PK-SK), location(FK-->Location(name))
Drawer_Register: registerid(FK-->Register(id)), drawer(FK-->drawer(id)), assignment_date  GD; PK: (registerid, drawer, assignment_date)
Transaction: id(SK), register(FK--> Register(id)), item_sold(FK-->Product(id)), qty NN, date GD, price_sold NN ; PK: (register, date)
Product: id (SK), name(PK), base_price
nhwillyAuthor Commented:
Thanks for the help.

Actually, this isn't a many to many for Drawers and Registers.  A Drawer can only have one register and it can only have one Period.  

What's really happening is the Drawer is defined by the Period and the Register.  Can the Period just stand by itself and not be attached to either the Organization or the Location?  That inherently means that the current period is defined system-wide and that's not right; all organizations can't move in sync.  

Maybe DanR has it right and Period should just inherit from Organization, although I don't think that really changes the problem as much as it just moves it up a level.

I only use auto incrementing integers for primary keys (I guess making them surrogate keys) on tables/databases of this nature, so let's see if I follow you (skipping the unrelated columns for the moment):

Organization: id(PK)
Location: id(PK), organizationId(FK)
Register: id(PK), locationId(FK)
Period: id(PK)    ?FK <--------------------------------this is where the confusion is for me.
Drawer: id(PK), registerId(FK), periodId(FK)
DrawerTransaction: id(PK), drawerId(FK)

nhwillyAuthor Commented:
Yeah, Dan, I think you've got it.

Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.