Need Database Field Design Advise PLEASE

[stann]

Hi

I have to write a database for an car auction to keep track of data and lane assignments for 8 car lanes. Each lane will possibly contain the information of up to 200 cars. I think I have to write 200 A fields and 200 B lane fields and so on. The labels would have to be

A1 to A200, B1 to B200 etc. Right off the bat I would be dealing with 1600 fields. with 3200 lookups and at least 4000 scripts because of what has to happen to the information. Does anyone have any insight into a more productive way to do these fields. Just the typing alone would take a week solid. THese are just lane location assignments and they are dynamic. I really am hoping someone has some good advise on this one. I will be watching closely to answer questions all day. Thanks

STAN

FileMaker Version: 7

Platform: Mac OS X Panther

[Reed]

why not have one record for each car, where the car description is one field and the lane assignment is another?

[stann]

Good Idea but maybe not easy to see vacancies and places to block book cars.

That is why it is difficult.

STan

[Reed]

I am assuming that you will have a set of lane assignments for each auction event. So maybe you could set up 4 tables:

Cars

Events

Lanes

Join

the join table would have three foreign keys. CarID, EventID, and LaneID.

When a new event is created, you can create as many records in the join table as there are lanes used in the event. You could then assign CarID values to the various join records. Of course you would view these records in portals in a table other than the join table (i.e. the event table layout). This portal could show which lanes were being used for each event and which cars are in them.

Maybe someone else could chime in with a more elegant solution.

Dana

[Fenton]

Showing vacancies if often best done in a "Grid" table. The records for the grid are however many you need to "reflect" your real "entities," ie. the number of possible entries, incl. blanks. The "columns" are produced by relationships/related fields.

The column entity in this case could be either lanes or cars. But there's a small fixed number of lanes, so it's the obvious choice.

Grid Table Fields:

SerialID, auto-entered number, 1, 2, 3, etc..

_cLaneA, unstored, = "A" & SerialID

_cLaneB, "" = "B" & SerialID

Cars Table Fields:

CarID (auto-enter serial, not needed for this problem)

Car

Lane

GridID

Lane_Grid = Lane & GridID

[or, in 7, just one text field?]

Relationships from Grid to Cars:

_cLaneA = Lane_Grid

_cLaneB = Lane_Grid

etc. for 8

The ONLY stored data in Grid is the SerialID. The number of records is however many you need to get all the cars in.

200/8 = 25 records (create with Loop)

Fields on layout in Grid, 8 across:

_cLaneA::car

_cLaneB::car

etc.

When you enter a Lane letter and Grid# in Cars, it will then show on the Grid.

You may want to be able to click on a blank space on the Grid, to add a car.

That requires:

1. Set the Lane letter and SerialID (GridID) into global(s), via their self_relationship.

2. In cars, pick a car.

3. Set the globals into the Lane and GridID fields.

Or

1. Pick the car first.

2. Click the space in Grid

3. Pass the coordinates to Cars

4. Set that car's Lane and GridID

Refresh the Grid screen.

As far as scripts in Grids, FileMaker 7 will save multiple scripts, by passing the Lane & GridID as a parameter (which you can separate; or just use them always together).

I'll get back to this later if you questions (or the above is wrong). But I've got Letters to do; I wish the client could upgrade to 7 now :-|