Difficult database design/relationship work

[Steven Dabkowski]

Hi there,

I have recently gained a new client. The client is a Nautilus Gym in the local area. They wish to move from their current paper file system to a digital system encompassing FileMaker on both Mac and Windows computers and iPads for roaming around the gym accessing/updating their members data.

I am stumped on this one tho. Member details and payment stuff is simple, all done on that front. But recording the exercise data is going to be difficult. These databases have to be quick, but they are going to be HUGE. For every exercise (of which there are 32 in total, not all used by every member, that would be Chuck Norris gym work!) I need to be able to log weight settings, seat positions, reps and other information. The average member does ten exercises. I also need to keep a history of these values so that the Gym can track the progress of its members and tailor programs to work for them.

I am without ideas on how to work all of this out! I could set up a table for every exercise and used ID's from the MemberDetails to link it and then display it all later on another layout. But that is going to get very messy very quickly and with that many relationships and lookups happening at once I fear that it will slow down substantially. As a rough guide, the gym has approx. 200 active members.

There is another added layer of complexion as there needs to be reports generated that combine data from every exercise, such as generating leaderboards.

Anyone with any ideas? Any help is greatly appreciated!!!!!

Steven Dabkowski

[Vaughan]

You may be re-inventing the wheel. This solution has been around for many years, is mature and is actively being updated and improved.

http://www.xercisepro.com/

[Steven Dabkowski]

Ha I probably am.

I had never heard of that software before.

Unfortunately the client really wants a mobility component (hence the iPad/iPhone). Xercise Pro doesn't seem to support that or offer a web interface.

Someone on another forum suggested this:

"Think of it this way: Each time a member comes to the gym, start a "workout invoice", one such record for each visit. Each equipment or excersise that the member chooses is a product that they "buy" and it will be added to the LineItems portal of the "invoice". Instead of price and quantity fields like an actual invoice, the fields in each line item record would record whatever data about each excersise that you need to record."

Sounds like a great idea to me. Do you think it would work?

Thanks again.

Steve

[comment]

Each equipment or excersise that the member chooses is a product that they "buy" and it will be added to the LineItems portal of the "invoice".

...

Do you think it would work?

It depends: typically, all line items have the same fields regardless of the product {ProductID, Price, Quantity}. There may bee additional fields that apply only to certain products such as Color or Size.

If you can organize your "line items" table so that it fits all 32(?) types of exercise, and do this without destroying the meaningfulness of the data (e.g. without lumping color and size in the same field) then yes, it should work.