If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
"=?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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
When starting Word 2000, the installer keeps starting. Using XP . | MYost | General Discussion | 0 | September 22nd, 2004 09:31 PM |
multiple instances of outlook starting | George Sohos | General Discussion | 0 | August 4th, 2004 04:41 PM |
Sum a Row Starting with First non Zero Value | rca | Worksheet Functions | 3 | July 8th, 2004 08:17 PM |