Question about relational tables in FMP 9

For simplicity sake, let's say I want the database to keep track of
the type of car and its color that passes my window every day. Each
day is a different record and the type of data is numbers. My street
is not very busy, so many days some types of cars will be recorded as
0. So should I divide the data into two tables; the type of car table
and the color of the car table? For example, the fields for the car
table are truck, luxury car, compact car, minivan and SUV. The fields
for the color car table are red, green, black, blue and plaid. If
they should be divided into two tables, how do I relate them?

On 2008-08-20 17:58:39 -0700, geeksquid said:

> For simplicity sake, let's say I want the database to keep track of
> the type of car and its color that passes my window every day. Each
> day is a different record and the type of data is numbers. My street
> is not very busy, so many days some types of cars will be recorded as
> 0. So should I divide the data into two tables; the type of car table
> and the color of the car table? For example, the fields for the car
> table are truck, luxury car, compact car, minivan and SUV. The fields
> for the color car table are red, green, black, blue and plaid. If
> they should be divided into two tables, how do I relate them?

In Filemaker, the data for both types of cars and colors would be
treated as value lists (look this up in the Help), rather than separate
tables.

Some of us use actual tables to hold value list values, rather than
storing them in the file, but for basic one user solution, this is not
necessary. Separate "reference" tables like this make administration of
values easier in multi-user shared solutions. Otherwise it's just
easier to create value lists using custom values you type into the
dialog box for your values. These lists are editable at any time.

Then create one table for "Car Sightings" with the fields for make,
model, type, color, direction of heading and attractiveness of driver.
You don't need to have related tables until you get into tracking
data like how many times a particular car passes. Then you'd need a Car
table with a field for License Plate, and a one-to-many relationship to
a Sighting table.

--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA

In article
,
geeksquid wrote:

> For simplicity sake, let's say I want the database to keep track of
> the type of car and its color that passes my window every day. Each
> day is a different record and the type of data is numbers. My street
> is not very busy, so many days some types of cars will be recorded as
> 0. So should I divide the data into two tables; the type of car table
> and the color of the car table? For example, the fields for the car
> table are truck, luxury car, compact car, minivan and SUV. The fields
> for the color car table are red, green, black, blue and plaid. If
> they should be divided into two tables, how do I relate them?

Database design really depends on a lot of factors - one important
thing to consider is what reports you want to get out of the system.

For such a simple example there's probably no need for a relational
database at all, but for the sake of the example, you could have a
database something like:

DaysRecords Table
RecordDate Date field

Traffic Table
RecordDate Date field
VehicleType Text (Value List: Truck, Luxury Car, ...)
VehicleColour Text (Value List: Red, Green, ...)

The two Tables would be joined using the RecordDate field. You would
have a data entry Layout that displays records from the DaysRecords
Table and has a Portal displaying the related Traffic Table records.
Entering new Traffic records via the Portal would mean the DaysDate is
automatically set correctly for the Relationship.

Reports could be easily displayed / printed from the Traffic Table,
summarised by date (full date, month, year, etc.), VehicleType, or
VehicleColour - these reports could also break each grouping down by
sub-groups.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)