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...
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.


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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now