Solved

Help with relations in a database design

Posted on 2010-11-23
9
57 Views
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.

Definitions:
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.

Suggestions?
 The current database layout from MSSQL 2008
0
Comment
Question by:nhwilly
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 15

Expert Comment

by:danrosenthal
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.
0
 

Author Comment

by:nhwilly
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.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 34200916
<<What have I done wrong.  This plain just doesn't look right.

Suggestions?>>

> 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...
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:nhwilly
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)
etc....

0
 
LVL 15

Accepted Solution

by:
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)



0
 

Author Comment

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

Thanks.
0
 
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 46
MS SQL Backup 24 70
sql server query? 6 26
Creating Alerts in sql sever 2 13
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

15 Experts available now in Live!

Get 1:1 Help Now