 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / December 2004Starting 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==?=" <Airron@discussions.microsoft.com> wrote in news:CEFB3D2F-AB16-485C-9486-F721E28C9E96@microsoft.com: > The casino is now broken down into 3 separate areas: Existing, > Expansion, and Sprung. Each area is broken down into different zones, [quoted text clipped - 3 lines] > 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? >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 [quoted text clipped - 23 lines] >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) >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; [quoted text clipped - 9 lines] > >Aaron -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. 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 > That suggests something similar to Tim's suggestion of > [quoted text clipped - 37 lines] > > 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 >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 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. >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 [quoted text clipped - 3 lines] > >Aaron -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. 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 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. >Actually, we use the Vendors and machine types interchangably. While the >Vendors do in fact provide us with different machine types, we don't [quoted text clipped - 18 lines] >I'll work on implementing your suggestions >aaron -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. 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 [quoted text clipped - 38 lines] > > Please respond in the newgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.