Starting from Scratch...Again.

Okay, I have relied on these forums quite a bit for the old version of the
database I have created, but I now need to expand it. I'm going to attempt
to start from scratch and create a new split database with more
functionality, and fewer mistakes this time, so I'm going to ask for
suggestions on layout before I begin.

I work in a casino, and we count the number of people playing the machines
every 2 hours (12 am, 2 am, 4 am...), to track machine popularity, as well as
labor needs.

The casino is now broken down into 3 separate areas: Existing, Expansion,
and Sprung. Each area is broken down into different zones, and each zone has
games from at least 2 vendors, as well as blackjack and poker.

We are currently broken down into 3 8-hour shifts, with each shift counting
4 times.

I would like to break it down so the data entry can be done by floor and
shift, hopefully with some sort of record showing which shift/area has input
their data.
I also need people to be able to run, print, and email reports to the
operations manager until he is comfortable doing them himself.

If anyone has any suggestions for table structures and relationships, I
would love to hear them!
thanks
Aaron

I forgot to mention: In the old database, I started with a monolithic
design for the raw counts, with a ton of field names specifying vendor name
and area, it had a combination primary key, using the date and time of the
count. The drawback was that you had to enter the counts from the entire
casino, (at that time, spanning 2 of the now 3 areas) all at the same time.

So - I guess I'm also asking for suggestions for a more logical and easier
to use table, record, and primary key layout.
aaron

"=?Utf-8?B?QWlyLXJvbg==?=" wrote in
:

The casino is now broken down into 3 separate areas: Existing,
Expansion, and Sprung. Each area is broken down into different zones,
and each zone has games from at least 2 vendors, as well as blackjack
and poker.

We are currently broken down into 3 8-hour shifts, with each shift
counting 4 times.



The main thing to count is presumably

Observations(*ZoneNumber, *GameID, *TimeAndDate, NumPeople)

with a PK based on the first three. Other obvious candidates are

Games(*GameID, EnglishName, Vendor, MaxPrize, etc)

You might want a controlling table like

IsPlayedIn(*GameID, *AreaCode)

to note which games are played in which area, and make Observations(
ZoneNumber, GameID) a FK referencing that table, rather than having two FKs
pointing at the Zones and Games tables.

You can keep track of which Zone is in which Area in the Zones table thus:

Zones(*ZoneNumber, AreaCode, ManagersName, NumberOfFireExits, etc)

and so on.


Hope that helps


Tim F

Hi Aaron,

What is it you're actually counting? From what you've said I get the
impression that each zone has a number of machines from various vendors.
Are you recording the number of people in each zone at the time in
question, or which individual machines have a person in front of them,
or what?

On Sun, 26 Dec 2004 08:13:01 -0800, "Air-ron"
wrote:


Okay, I have relied on these forums quite a bit for the old version of the
database I have created, but I now need to expand it. I'm going to attempt
to start from scratch and create a new split database with more
functionality, and fewer mistakes this time, so I'm going to ask for
suggestions on layout before I begin.

I work in a casino, and we count the number of people playing the machines
every 2 hours (12 am, 2 am, 4 am...), to track machine popularity, as well as
labor needs.

The casino is now broken down into 3 separate areas: Existing, Expansion,
and Sprung. Each area is broken down into different zones, and each zone has
games from at least 2 vendors, as well as blackjack and poker.

We are currently broken down into 3 8-hour shifts, with each shift counting
4 times.

I would like to break it down so the data entry can be done by floor and
shift, hopefully with some sort of record showing which shift/area has input
their data.
I also need people to be able to run, print, and email reports to the
operations manager until he is comfortable doing them himself.

If anyone has any suggestions for table structures and relationships, I
would love to hear them!
thanks
Aaron

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

What we're actually counting is the number of people that are playing each
type of machine in each zone. ie - Zone 1: 23 VGT, 5 SDG, 15 Cadillac Jack;
Zone 2: 48 VGT, 19 Rocket....

The zones don't necessarily all have uniform numbers - so that will have to
be a text field.

Right now I'm just trying to set up a good table structure, with proper
relationships and whatnot, which should enable much easier querying and
analysis. I also want to keep an eye on making the data entry and error
checking easy.

Aaron

That suggests something similar to Tim's suggestion of

Observations(*ZoneNumber, *GameID, *TimeAndDate, NumPeople)
with a PK based on the first three.

Perhaps:

tblObservations
ZoneName* - FK into tblZones
MachineType* - FK into tblMachineTypes
ObservationDateTime*
NumPlayers
Created (date/time record was created)
CreatedBy (user who entered the data)
Checked (date/time record was checked)
CheckedBy (user who checked the data)

with other tables including

tblZones(*ZoneName, other stuff)

tblMachineTypes (*MachineType, Maker, Model, other stuff)


On Mon, 27 Dec 2004 07:27:02 -0800, "Air-ron"
wrote:


What we're actually counting is the number of people that are playing each
type of machine in each zone. ie - Zone 1: 23 VGT, 5 SDG, 15 Cadillac Jack;
Zone 2: 48 VGT, 19 Rocket....

The zones don't necessarily all have uniform numbers - so that will have to
be a text field.

Right now I'm just trying to set up a good table structure, with proper
relationships and whatnot, which should enable much easier querying and
analysis. I also want to keep an eye on making the data entry and error
checking easy.

Aaron

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Okay, here's what I have so far:

tblShifts
Shift number - 1toM related to shift in Ctimes
Shift Name - (Day, Graveyard, etc.)

tblTimes
CTime - (12Am - 10PM) 1toM related to Ctime in tblCounts
Shiftnumber - related to shift table

tblFloors
Floor - 1toM related to Floors in Zone table

tblZones
ZoneID - autonumber for zones 1toM related to Counts Table
Zone Name
Floor - Related to the flloor table

tblVendors
VendorID - Autonumber, 1toM related both to junction table and tblCount
Vendor Name
Vendor Abbreviation

tblVendorZone is a Junction table to relate the MtoM Zones to Tables
VendorID
ZoneID

tblCount - where the actual counts will be stored
GDay - Related to the entrylog table
Time - Related to the time table
ZoneId - Related to the Zone list table
VendorID - Related to the Vendor list Table
Count - Where the actual data is

tblDateEntry - this is where I plan to keep track of which days, shifts, and
floors have been entered, through a myriad of yes/no fields that switch to
yes when someone enters the data for the particular date/casino floor.

I'm hoping to make this fairly easy to use - but is this design actually
going to make entering an entire shift's worth of data harder?

I'd appreciate any other comments you have for me on this layout
thanks
Aaron

Thanks -

I think I did almost exactly what you mean - My other post details it,
although my terminology may not be correct.
Thanks again
Aaron


"John Nurick" wrote:

That suggests something similar to Tim's suggestion of

Observations(*ZoneNumber, *GameID, *TimeAndDate, NumPeople)
with a PK based on the first three.

Perhaps:

tblObservations
ZoneName* - FK into tblZones
MachineType* - FK into tblMachineTypes
ObservationDateTime*
NumPlayers
Created (date/time record was created)
CreatedBy (user who entered the data)
Checked (date/time record was checked)
CheckedBy (user who checked the data)

with other tables including

tblZones(*ZoneName, other stuff)

tblMachineTypes (*MachineType, Maker, Model, other stuff)


On Mon, 27 Dec 2004 07:27:02 -0800, "Air-ron"
wrote:


What we're actually counting is the number of people that are playing each
type of machine in each zone. ie - Zone 1: 23 VGT, 5 SDG, 15 Cadillac Jack;
Zone 2: 48 VGT, 19 Rocket....

The zones don't necessarily all have uniform numbers - so that will have to
be a text field.

Right now I'm just trying to set up a good table structure, with proper
relationships and whatnot, which should enable much easier querying and
analysis. I also want to keep an eye on making the data entry and error
checking easy.

Aaron

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

On Mon, 27 Dec 2004 10:41:03 -0800, "Air-ron"
wrote:

tblDateEntry - this is where I plan to keep track of which days, shifts, and
floors have been entered, through a myriad of yes/no fields that switch to
yes when someone enters the data for the particular date/casino floor.

This is almost certainly the wrong approach.

First, if you have a table with a "myriad" of yes/no fields, you are
storing business processes (or maybe the layout of the casino) in the
*structure* of the database, which means that any change (e.g.
modification of the zones) means you have to restructure the table and
all queries, forms, reports and code that use it.

Second, it's very hard work to write the code to ensure that these
yes/no fields would be updated 100% reliably depending on the state of
the data input.

Third, it would be storing redundant data. With a well-designed
database, if you want to find out what data has been entered, you just
run a query that tells you. (Usually, it makes more sense to use a query
that finds the gaps in the data so you know what's missing.)

I'm hoping to make this fairly easy to use - but is this design actually
going to make entering an entire shift's worth of data harder?

Get the data structure right before you worry about the user interface.
One of the great things about relational databases is that they separate
the user interface and the data structure (unlike spreadsheets, where
the interface and the structure are one and the same). This means you
can (and should) first design the structure to fit the real-world
entities you are modelling, and then build a user interface to fit the
real-world users.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

You're absolutely right!

I do have another question -

I am relating my Zones to my Vendors in a M2M relationship, so I have a
junction table. However, if either the zones or vendors change, that table
will have to be changed, correct? I am now envisioning a form that shows the
vendors, zones, and then a crosstab-type data sheet to relate them. Do I
need to set that up as a table, or would a query work?

Aaron