Help with relations in a database design

Posted on 2010-11-23
Medium Priority
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...
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 2000 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 23

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

615 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