Database to record sale of transactions

[James T]

Hello all...!!!

I am in the process of designing a database for a company that sells suites
(i.e. grouping of ten seats) at a Sports stadium.

I have so far created the following tables:

Customers - general customer details.
Suites - this has 2 fields - Name of Suite and Sold (yes/no);

My issue is the following:

1) A particular suite (lets say number 1001) can have 2 different prices.
For example $2,500 if sold by itself, or $2,000 if sold with other items;

2) When doing data entry to record the sale of a suite the user would only
like to see those suites that are left to sell;

Any thoughts on:

1) What additional tables I might need to record the sales transactions and
also account for the different pricings;

2) How would I setup the data entry form to only show those suites that are
still available for sale?

Any help would be greatly appreciated.

Regards

James

 

[TedMi]

Presumably, you sell suites repeatedly, for different events. Your two tables
do not allow for this. You need an event table that will track sales of each
suite for a particular event.
Customer table - details of customer only, including Customer ID as Primary
Key.
Event table:
Event ID (PK) could be autonumber
EventName
EventDateTime

EventPrice
PriceID (autonumber, PK)
EventID (Foreign key to Event.EventID)
Price
(This allows selling suites at different prices for different events, and
any number of price levels per event).

Suite table:
SuiteNo
SuiteName

SuiteEventLink
SuiteNo (FK to Suite.SuiteNo)
EventID (FK to Event.EventID)
CustomerID (FK to Customer.CusotmerID)
PriceID (FK to EventPrice.PriceID)
Other fields as required to describe this particular suite sale - e.g.
link to list of names of suite guests, catering requirements, handicapped
access requirements etc.
To ensure that a suite gets sold only once per event, set a compound PK on
SuiteNo, EventID

To determine which suites are available for an event:
Set up a form with a control where you enter (or select from a dropdown) an
EventID.
Let's call this control ThisEvent. Add a subform with this record source:
WARNING: THIS IS UNTESTED "AIR CODE"!

Select SuiteNo, SuiteName From Suite
Where SuiteNo not in
(Select SuiteNo From EventSuiteLink Where EventID = Parent.ThisEvent)

The above design is predicated on a pricing model where the price does *not*
depend on which suite is purchased. The pricing data gets a lot more complex
if it has to reflect differences in suite capacity or amenities.