Help Normalize (1 Viewer)

JahJr

Andy
Local time
Today, 00:37
Joined
Dec 3, 2008
Messages
93
I'm continuing a post that I had under Queries but it turns out it is a table issue.
Link to other post:http://www.access-programmers.co.uk/forums/showthread.php?p=1228190#post1228190
I have created 4 tables now
tblPOS
tblOpenClose
tblBartenders
tblBartenderTips

tblPOS has the following fields:
TransactionID, BusinessDate, Food Sales, Liquor Sales, Beer Sales, Daiquiri Sales, Wine Sales, T-Shirt Sales, Tax, Comp, Disc, Visa, American Express, Discover, Master Card, Cash Paid Out, Cash Paid In, Total Cash, Donations, Gift Certificates, Credit Card Tips

tblOpenClose has the following fields:
TransactionID, BusinessDate, Open Drawer, Close Drawer, Cash Tips, Open Bartender, Close Bartender, Number of Bartenders

tblBartenderTips has the following fields:
TransactionID, BusinessDate, EmployeeID, Amount

tblBartenders has the following fields:
EmployeeID, Name

I don't know if this is right or wrong or if it should be split up some more. I'm also unsure about the primary keys and relationships> Any pointers or direction will be greatly appreciated.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:37
Joined
Feb 19, 2002
Messages
43,328
tblPOS should be more like:
TransactionID
BusinessDate
TranType
TranAmt

TranType would be Food Sales, Liquor Sales, etc.

tblOpenClose
OpenCloseID
BusinessDate
NumOfWorkers
DrawerType
OpenAmt
CloseAmt

DrawerType would be Drawer, Bartender, etc.

tblBartenderTips should become just tblTips
TipID
EmployeeID
BusinessDate
TipAmt

The Name field in tblBartenders should be broken into FirstName and LastName or at least renamed to EmployeeName. "Name" is the name of a property and will be nothing but trouble when working with VBA. You allso need to avoid other property and function names such as Date, Month, Password, Type, etc. as well as eliminating all special characters and embedded spaces from column names.
 

JahJr

Andy
Local time
Today, 00:37
Joined
Dec 3, 2008
Messages
93
Wow, this is all becoming a little clearer now. A few questions...
1. In tblOpenClose I can't picture how your recommendation is going to work. I might not have been very clear in my original post. In my current data. I have the following fields. Open, Close, Open Amount, and Close Amount. Open and Close are just the name of the employee who opened and who closed. Open Amount and Close Amount are the totals for the cash register at the beginning of the night and at the end of the night. Not sure how drawer type will work into this.
2. What would be the keys in each table and would ...ID be the AutoNumber?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:37
Joined
Feb 19, 2002
Messages
43,328
I added drawertype because I misunderstood what open/close bartender were. I thought they were names for additional drawers. You don't need the drawer type but you can add OpenedBy and ClosedBy.

Each table will have an autonumber primary key named something appropriate for the table. Naming all PKs "ID" just causes unnecessary confusion.
 

Users who are viewing this thread

Top Bottom