More on poor table design

[Lisa Reber]

Hi - am trying to improve and normalize my tables as
follows:
Contacts currently has multiple fields with repetitive data
regarding car show registrations. The proper(?) way seems
to be to set up a car registrations table with pertinant
fields, then delete all those from Contacts. But real-world
wise, people tend to bring the same car(s) to the show
year after year; my first attempt would have me entering
the same data every year. So date and amt paid will change
every year, but car(s) might or might not. (I'm thinking
while writing here . . .)
Does this sound like the way to go:
Table 1 - contacts
Table 2 - cars owned / shown
Table 3 - car registration details
And as far as entering details, do I want something like
Main Form: Contacts
subform: vehicles owned
sub-subform: car registration

End result - I need to know who has brought a 'vette in
the past; how much money have we received for the show;
how many pre-1920 cars are coming, etc.
Thanks for the input

 

[Pavel Romashkin]

The dat astructure you are planning is exactly what I'd recommend.
I would not use a sub-subform; instead, I would put, like you said,
Contact on the main form, Vehicles owned on a continuous subform on the
left, and registration details on a subform on the right. For the
Details sf, I would not link parent/child fields; instead, I would
filter it by the VehicleID from the Vehiches subform in the Vehicles sf
Current event. I think this kind of design looks neater.
As far as reporting per customer or per car, your planned data structure
is perfect for that.
Good luck,
Pavel

 

[Rolls]

tblCars (unique autos) ENTITY
CarID (PK)
CarMake
CarModel
CarYear

tblOwners (unique individuals) ENTITY
OwnerID (PK)
OwnerFName
etc.

Many owners : many cars

tblOwners-Cars <-- Subform RELATIONSHIP
OwnerID (FK) <-- cboBox to Owner full name
CarID (FK) <-- cboBox to car description
DateEntered
Fee
DatePaid
AmountPaid
etc.

resolves which owner brings which cars each year.