Question about proper database structure with FM7

[J Meredith]

I have a question for the Filemaker whizzes out there.

I am the owner of a graphic design firm who has recently taken on doing contract work for a local marketing company. We currently use a filemaker database to track all of our clients. I would like devise a simple solution for making proofs available online to customers through Filemaker 7. From the mucking around I have done at this point this looks possible.

We currently have a server running OS X 10.3 with a small website being hosted specifically for serving client proofs. We currently drop our proofs in folders for the clients and then provide a URL for them to download or view the proof in their browser. This works, but requires a lot of work to check what version we are posting and then send the appropriate URL via email.

The ideal solution would be to have a database open on the server, which is available via the web. This database would allow a customer to log in and view all of their proofs with the same username and password each time.

For the back-end, it would be nice to simply add an image to the database under the customers record (which I understand can now be done easily with a link to the file rather than embedding in the database) and that be available to the customer when they log in.

My delima is how to structure the database so that customers only have access to their records and not everyone else's records. The permissions structure seems to be very thorough in FM7 but I am unsure of how to structure the DB with the new Tables capability. It would seem that each customer should have a table but there is not way to create a table and then duplicate that table as we add customers. IT appears as though we would have to create each new table from scratch. If this were possible I would create a table and then assign privileges to the individual tables.

Please realize I am not a DB guru, so I am not the most experienced. I do understand relational DB ideas for the most part, but I am unsure about this structuring idea.

I ask someone here in the office that has some experience with databases and her suggestion was to create tables for each of the sections with a key field residing in the first table: Customer(key), customer info(key relationship), customer proofs(key relationship), etc. But then I am in a quandary as to how I would limit the customer to only the record or view that shows their info in each of the tables.

I am I approaching this from the wrong perspective?

I hope all of this makes sense and any help would be appreciated. Please keep in mind that we are a small design firm with limited resources, thus hiring someone to do this at this time is not feasible (though I would certainly like to if I could )

Thanks again for the help.

Jamie Meredith

i4-Design.net

--------------------

Jamie Meredith - President

ICTHUS Digital Services, Inc.

i4-Design

Dayton, OH

-----

icthusdigital.com

i4-design.net

Version: v7.x

Platform: Mac OS X Panther

[Wim Decorte]

Hi Jamie,

You should probably sit down with a FileMaker consultant/developer to outline this properly.

You wouldn't create one table per customer. You would have one Customers table and one table for all records (for all customers). One of the fields in that records table would hold the customers unique ID. A relationship between the customer unique key in the Customers table to the unique customer key in the Records table will pull up only the relevant records for one customer.

HTH

Wim

[Steven H. Blackwell]

The good news is with the new unfified security model that the permissions will carry through to the Web. Don't plan for this to be on-line until mid to late Summer.

Steven

[J Meredith]

Thank you for your responses. Unfortunately I don't feel my question was understood maybe. I understand the relationship between unique IDs and other tables. The questions maybe more easily stated by asking, how under the new structure of FM7 would I restrict a user logging in from the web to only their customer record and not all the records in the database?

As I understand it now, when using the web interface, the whole database is presented to the person accessing the site, unless specific permissions are set for each and every customer that is entered. If you have walked your way through the new permissions structure, you will notice that is very deep and in my opinion difficult to use in that you have to click through so many menus to set the permissions for a single user.

As stated before, we do not have the resources to hire a consultant and I believe that with the right direction from a helpful, knowledgeable person, I can lick this. There are few things in life that I have paid others to do, when I know all I have to do is gain knowledge to overcome the obstacle. Call it cheep or call it resourceful, this is my approach to all things in life.

Any further insight would be appreciated.