Starting from Scratch...Again.

[Air-ron]

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

 

[Air-ron]

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

 

[Tim Ferguson]

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

 

[John Nurick]

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?

 

[Air-ron]

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]

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)

 

[Air-ron]

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

 

[Air-ron]

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]

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.

 

[Air-ron]

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

 

[John Nurick]

Hi Aaron,

First, are you absolutely certain sure that a straight M:M relationship
between Zones and Vendors is what you need? I'd have thought that what
you have in each Zone is one or more types of machine (i.e. a M:M
relationship between Zones and MachineTypes), while each Vendor supplies
one or more types of machine (i.e. a 1:M relationship between Vendors
and MachineTypes).

In other words there's no direct relationship between vendors and zones,
but an indirect one via the type of machine installed in each zone. (In
that case it's still very easy to generate queries for questions like
"Which vendors are represented in each zone?"

Either way, though, your question about the user interface for the M:M
relationship is valid (whether it's Zones and MachineTypes or Zones and
Vendors). I guess you're envisaging a form with a grid that has zones
down the left side and vendors or machines across the top, or perhaps
the other way round. This is quite easy to generate using a query (often
but not always a crosstab), but queries like this are necessarily
read-only so it's not suitable for data entry.

The simplest way to enter or edit data in a M:M relationship in Access
is usually to use
1) a form bound to one table (Zones)
2) on this form, a continuous subform bound to (a query on) the junction
table
3) on the subform, a combobox bound to MachineTypeID or VendorID. The
combo's RowSource should be a query that returns
MachineTypeID, MachineType
or
VendorID, VendorName
in alphabetical order. (Because the M:M relationship is symmetrical, you
could also bind the main form to MachineTypes (or Vendors) and the
subform to Zones.

There are various techniques for creating an editable grid, but IMO
they're more trouble than they're worth except when specific data entry
requirements demand it.

 

[Air-ron]

Actually, we use the Vendors and machine types interchangably. While the
Vendors do in fact provide us with different machine types, we don't
discriminate between the different types when counting customers, as it's
done by hand. (counting the number of people on each of 4 vendors is
difficult, if it were broken down to 20+machine types as well, our customer
service people would never be done recording the data)

While the changes in the casino layout don't happen often, they do happen,
and it would be nice to be able to adapt - ie new vendors, change the layout
of the zones, expand the casino...

I tried something, and maybe you can tell me if it'll work. I used an
append query to fill a table that has all of the current vendors and all of
the current zones. there is also a yes/no field, that I used to insert the
current layout. Will this still allow my relationships, or will that destroy
them because the table contains a row with each vendor/zone combination.
(also I fear that this would be hard to update once I've moved out of this
dept - one of my main design goals here is that it's easy to use and flexible
enough that once I'm out of the dept, people can still use it)

I'll work on implementing your suggestions
aaron

 

[John Nurick]

If I understand you right, you've got a table like this (it would make
things easier if you provided this informatoin rather than expecting
people to try and work it out for themselves:

tblZonesVendors
ZoneID* - FK into tblZones
VendorID* - FK into tblVendors
VendorHasMachinesInThisZone - Yes/No

This is a junction table implementing a M:M relationship between Zones
and Vendors. The Yes/No field is redundant: the presence of a record in
this table means that that vendor has a machine in that zone, and the
absence of a record means that the vendor doesn't have a machine in that
zone.

 

[Air-ron]

Thank you for your help

If I understand you right, you've got a table like this (it would make
things easier if you provided this informatoin rather than expecting
people to try and work it out for themselves:

tblZonesVendors
ZoneID* - FK into tblZones
VendorID* - FK into tblVendors
VendorHasMachinesInThisZone - Yes/No

This is a junction table implementing a M:M relationship between Zones
and Vendors. The Yes/No field is redundant: the presence of a record in
this table means that that vendor has a machine in that zone, and the
absence of a record means that the vendor doesn't have a machine in that
zone.