Hi,

I need to create tables which stores info about theatres in different states of the country. The info is as follows:

State
Town
Theatre
Location
Seating Capacity
Sound System...few more general info related fields
Rate for screening an ad-film - current & previous year
Rate for screening a visual slide - current & previous year....few more rates related fields

Points to be considered:
1)The total no. of records is more than 10,000.
2)There are about 30 States
3)Theatre may or may not have the rate for ad-film/visual slide.
4)Rate usually change once in an year, therefore, need current & previous year.
5)Estimate to the client may either go for the ad-film or for visual slide or both. Mainly its for ad-films
6)Estimate to the client may go either on the current rate or previous year rate. At times, for a particular state it might be the previous rate and for others current.

Keeping the above in consideration, I could think of the following structures.

A) A Single table which has all the info

B) A Single table for each State with all the info

C) 3 Tables against each State.


tblState1_gen

Cinemaid(PK) - Id+first 5 chrs of State
Id - Autonumber
State
Town
Theatre
Location....so on

tblState1_film

Cinemaid(PK)
Current Rate for screening an ad-film
Previous Rate
few more fields related to ad-films

tblState1_Vis

Cinemaid(PK)
Current Rate for screening a visual slide
Previous Rate
few more fields related to slides.


It would be of great help if anyone could suggest which option would be the best or the structure needs to be defined in a different way.

Thanks in advance...

Kind Regards to all,

Tyna

From reading the first part it would seem you need a couple of tables or an optional third for towns.

tblStates
StateID (one-to-many with tblTowns.StateID)
State

tblTowns
TownID (one-to-many with tblTheatres.TownID)
Town
StateID

tblTheatres
TownID
Theatre
Location
SeatingCapacity
SoundSystem
etc...

Hi,

Thanx for your reply and advice.

Now, coming to the second part regarding the no. of records each table would be holding.

tblStates - 30-35 records

tblTowns - 7000-7500 records

tblTheatres - 10000 records approx
TownID
Theatre
Location
SeatingCapacity
SoundSystem
etc - additional fields are going to be 50-55

Keeping in mind the above, would it be better if the third table is maintained Statewise or a single one is preferred.

Do let me know your views.

Kind Regards,

Tyna

Why 50-55 more fields?

Let me start with the rate fields:

Ad-film Rate - 60seconds, 30, 20
Visual Slide Rate - 4wks, 13wks, 26wks, 39wks, 52wks
Audio Visual Slide Rate - 4wks, 13wks, 26wks, 39wks, 52wks
Rates to be maintained for Current & Previous year
Tax - Ad-film & Slides
Tax Terms - payable weekly/monthly/annually

Total fields: 29

Rates are not maintained proportionately, therefore, not to be considered as calculated

Then comes general info related to the theatre:

TownID,CinemaID,Theatre ,Location,SeatingCapacity,SoundSystem,No. of Shows, Language of the movie usually shown, Multiplex or single screen, Proprietor, Manager, Operator, Telephone No, Contract details, functioning or not....and the list goes on

Not sure I fully understand the structure but lets say Visual Slide rate. It only needs one field to enter the rate and another field which identifies the rate. If One theatre can have many rates then Rates belong in a separate table with TheatreID as the link field

Let me quote an example

The rate for screening a Visual Slide rate for:

4 weeks - 100
13 weeks - 310
26 weeks - 585
39 weeks - 830
52 weeks - 1040

(minimum order taken is for 4 weeks, duration of screening is standard - 5 seconds)

The rate for screening an Ad-film(ad-films can be of different duration & rate is quoted per week)

60 seconds - 200
30 seconds - 150
20 seconds - 100

Is the picture clear now? Hope I'm not confusing it further.