am i doing something wrong?

hello i need a urgent help on database design, and on my relationship tables to be precise.

i was doing an exercice on a small theater company where i have to keep a record of all the actors who are available to take part in a production and their future availability as well as keeping record of all productions including the play, playwright(s), date of performance and number of seats sold at each performance together with ticket prices(concessionary price available except on closing night).

an actor can be associated with more role in any given production(up to three).

the productions are:

1) A midsummer night's dream performed in old vicarage date:17-21 july 2001

2) Hamlet performed in village hall date: 8-12 april 2003

3) A midsummer night's dream performed in ben johnson's house date:22-26 july 2003

4) John gabriel Borkmann performed in village hall date:11-16 nov 2003

there are 30 actors involved, village hall seats 250 people and the others venus between 200 and 300, ticket prices from 10 to 15 full price and from 7 to 10 concession price.



i have created a relationship table but it seem to be wrong somehow. the tables i have got are:

actors: A_id A_name, A_surname, A_tel, A_spiciality(tragic, comic or supportive role), future availability (yes or no)

production: p_id, venue, play, playwrights, fullPrice_tickets, concess_tickets

previous roles:A_id, p_id, first_role, second_role, third_role

pefrformances:venue, perf_date, full_price tickets_sold, concess_sold.



now the probleme i am getting is i cant link venue from performances table with the one from the production table. 'cause when i tried to enforce the referencial integrity i get "no unique index found for the referencial field of the primary table". i also tried without enforcing the referencial integrity but when i created a query to give me the sum of each performance i get both of the village hall's performance under production 2 and the same under production 4.

this is driving me crazy . i really need help.

many thanks.

You are trying to link two fields in which none are the primary key - based upon your data above, I would make the venue in the performances table the primary key.



Better yet, I would create a field called "VenueId" (autonumber) and make that the primary key. Then I would create a field in the production table called "PVenueID" (number) as well and populate it with the autonumber values from the preformances table.



You need to link a Primary key (VenueId) to a Foreign key (PVenueId). The data types (text, number, etc.) from both fields in both tables must be of the same type.

moisea,

Welcome to aspfree forums. We welcome any posts you may have, but please post more appropriate subject lines such as "problems linking tables" or something of that sort, thanks.