Help with relations in a database design

Posted on 2010-11-23
Last Modified: 2015-02-09
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
Question by:nhwilly
  • 3
  • 2
  • 2
  • +1
LVL 15

Expert Comment

ID: 34199677
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.

Author Comment

ID: 34200488
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.
LVL 23

Expert Comment

ID: 34200916
<<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...
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

LVL 23

Expert Comment

ID: 34201030
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

Author Comment

ID: 34202436
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)

LVL 15

Accepted Solution

danrosenthal earned 500 total points
ID: 34207767
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)


Author Comment

ID: 34219405
Yeah, Dan, I think you've got it.

LVL 22

Expert Comment

by:Steve Wales
ID: 40597945
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question