Looking at the database, it looks like you may need some initial tables like this, and then more to model the financial parts, as I'm not sure how they all inter-relate...
An
Events table. This table will represent the many events that your business will handle/present. Not to be confused with
Shows. [I've left out the Home/Away and Inside/Outside fields for now, as not sure how the work.]
tblEvents
EventID (Primary Key, Autonumber)
ShowID (Foreign Key, Number)
StartDate (Date/Time)
EndDate (Date/Time)
StateID (Foreign Key, Number)
VenueID (Foreign Key, Number)
A
Shows table. Your current table contains repetitions of the same show names, differentiated only by date. This suggests you have/perform the same shows available for different times. Therefore, we need to model these...and we don't need to put the date in the show's name, as the date(s) of the show will be captured in the Events table.
tblShows
ShowID (Primary Key, Autonumber)
ShowName (Text)
ShowTypeID (Foreign Key, Number)
A
Show Type table that will determine, as the name suggests, the type of show that it is. We keep this in a separate table as, although it's related to the
Show, it's actually an entity in its own right.
[This is actually what you called Venue type, but based on the descriptions, it doesn't really described the venue as much as it does the show.]
tblShowTypes
ShowTypeID (Primary Key, Autonumber)
ShowType (Text)
A
States table. You could just have a list hardcoded into your database (as you do), but I think it's always better to have the data in a table where its manageable. Plus, should you expand (into Canada, say), then the hardcoded 50 state limit becomes an issue of design, rather than one where you can just add a new Canadian state into the table. I've made a field for key, one for the two letter code, and one for a full description.
tblStates
StateID (Primary Key, Autonumber)
StateCode (Text)
StateName (Text)
A
Venues table that will record information about the venues where events will take place. Always useful to maintain contact details, etc.
tblVenues
VenueID (Primary Key, Autonumber)
VenueName (Text)
Telephone (Text)
Email (Text)
Address1 (Text)
Address2 (Text)
ZipCode (Text)
I've not gone to the extent of thinking about fields like Vendors, People, etc, as I'm not sure what they are (vendor capacity? venue capacity?)
You may then want to think about your tables. Personally, I'd have a table for
Tables, with each table that you have individually logged/itemised. Not sure if you also have chairs or other furniture, which may also need considered. For the purposes of this, and to add a bit of complexity, let's assume not....
tblTables
TableID (Primary Key, Autonumber)
TableName (Text)
TableSize (Text)
TablePrice (Currency)
Now we need a way to link these tables to our event...but this is a many-to-many relationship, so we need a junction table for
Tables to Events...
tblTablesToEvents
EventID (Primary Key, Number)
TableID (Primary Key, Number)
I see we have a field for
Vehicles, but it appears just to be a quantity...let's create a table to store all our vehicle information...
tblVehicles
VehicleID (Primary Key, Autonumber)
VehicleModelID (Foreign Key, Number)
ModelYear (Number)
Of course, vehicles come in all sorts of
Makes and
Models....so we may need fields for those....
tblVehicleMakes
VehicleMakeID (Primary Key, Autonumber)
VehicleMake (Text)
tblVehicleModels
VehicleModelID (Primary Key, Autonumber)
VehicleModel (Text)
VehicleMakeID (Foreign Key, Number)
Now, like
Tables, there can be more than one
Vehicle to each event, so let's capture them in another junction table for
Vehicles To Events.
tblVehiclesToEvents
EventID (Primary Key, Number)
VehicleID (Primary Key, Number)
Right, now show me the money!!!
Well, you've got a lot of fields here. Not sure of how things relate, etc but looks like we need tables for
Costs and
Cost Types; one for
Tax Rates. There's talk of Shipping, so perhaps something for
Shipping Details. I wouldn't want to go further without understanding how these fields work and can be normalised.
But, reading this, do you get a better understanding of why we split out your single 'MASTER-Show-List' table into many more tables?
For now, rather than getting in a twist about averages, maxes, and mins, get your tables in a good working order.
