Starting my Database

[Kimmie]

Hi All,

I am not new to FM but not an expert by any means. In the past I have created FM databases to track sales and invoicing for tradeshows. I typically create a database a year per show. I want to create the database once and use the same one each show so i can track history, and other information i would like to keep in one place. I am starting from scratch and know this much. The main table will be the companies exhibiting, the problem i am having trying to figure out how to break down my tables. The main sale of the show is booth space, within this booth space will be unique booth numbers, sizes, prices, commissions, etc. but the payment schedule is three payments per sale. Originally i built the DB using a portal to capture the show and year, and the separate fields to capture the three payment schedule but because i am entering the data in a portal, I am having a problem being able to capture the data without making specific fields per year of each show. I am trying not to over complicate the db and I know there has to be a way to create the main fields without them having to be specific to each year? I would appreciate any suggestions. Thank you!

[Fitch]

I see the primary tables/entities as Exhibitors (Companies) and Booths (Sales).

The Booth record contains the info for that specific sale, with a field for year and/or a Shows ID that links to a Shows table that contains show-specific info that is used by all booths in common for a given show.

Hope that helps.

[Kimmie]

Hi Fitch,

It makes sense to have a show id but what I'm running into is there is different pricing per show. Should i create a separate table for each show?

[Kimmie]

OK...so I created the contacts database, created the show id (used the year), created the pricing database (100 items that can be sold per show). The pricing on all items changes each year. I have an ID on the items and price. Do I add 100 more records each year with new pricing? I have the booth sales data being entered into a portal from Booth Sales to the main contacts. How do I have the same field generate differently if the show ID has changed? I feel like a puppy chasing its tail!!

Edited January 17, 2008 by Guest

[Fitch]

You definitely do NOT want a new table for each show. You can either add new pricing records for each show or modify the existing prices. (When you make a sale, you should be using a lookup to pull the price into the sale record, so that it will be stored as sold, regardless of future pricing changes.)

In my mind the booth record IS the sale record. I.e., you make a sale to Company A; so you create a booth record with that company's ID so they are linked. You then proceed to enter in the sale info in that booth record: the year, the price etc. The price looks up from your pricing table.

If you're sitting on the company/contact record and it has a portal showing related booth records, you should be able to add a new record to the portal and edit it as needed.

If the related sale/booth/whatever you want to call it record has multiple items, then you might want to use another table for that and use a booth detail layout to edit the info rather than try to do it all through a portal on the contact.