Table Setup

[frinholp]

I am creating a database for an art gallery. The people I will be tracking in my database will be Artists, Potential Artists and Clients. I will be holding different types of data on each of these but some data will be common to all such as contact details, account created etc.

I see it as I have two options:-

(i) Create three seperate tables one for artists, one for potential artists and one for clients.

(ii) Create a Contacts table that will hold clients, artists and potential artists. I would have three table occurences of the contacts table in my relationships graph, one for artists, one for potential artists, one for clients. They would be dertimined by their type. If they are an artist an ArtistID would be generated for them, if they are a client a ClientID would be generated for them using their ContactID. I would then have a three other tables that would hold data specific to their contact type e.g studio for artists or delivery address for clients creating a relationship using their generated ArtistID and ClientID.

Would this second option be feasible and what would be the benifits?

Thanks in advance

Lee

[comment]

If you have any tasks that involve people from all three categories (e.g. birthday cards), then having separate tables only is not an option at all. Besides, potential artists are likely to become "real" artists - and moving records from one table to another on a regular basis is not a good practice.

Having four tables (one supertype, three subtypes) is the most "correct" solution - but not trivial to implement. Search the forums for "supertype" to find some previous discussions of this model.

A third alternative is a single table with all the fields necessary to accommodate all three types. Though not strictly correct from a data modeling viewpoint, it is quite easy to implement, and with the use of script triggers it can provide a user experience equivalent to the supertype/subtype model.

[frinholp]

Sorry it's took so long to reply. I've been away for a while.

Thanks for you advice comment. I have opted to go with the supertype/subtype model. I will have a contacts table (holding data that is common for both artists and clients) that will be the supertype and artist and clients tables that will be the subtypes. I will create a layout for adding an artist and one for adding a client.

In the case of adding an artist layout, do I base this layout on the contacts table or the artists table? How do I get filemaker to create a record for both a contact and an artist and automatically link the two to tables by their id's upon creation?

Any help would be appreciated or if anybody has any resources they can point me to that would assist me, I would be very greatful.

Thanks in advance

Lee

[comment]

In the case of adding an artist layout, do I base this layout on the contacts table or the artists table?

It's largely a matter of convenience. In any case, you must place fields from the "other" table on the same layout, allow the relationship to automatically create records in the other table, and validate that at least one field from the other table is filled out.

[frinholp]

Thanks again comment. That makes sense.

Could you please verify that I'm on the right tracks regarding the super/subtype model before I proceed any further?

Here is what I have created so far:

I have created a 'contacts' table that holds data common to an artist and a client. I have created two tables 'artist details' and 'client details' containing details specific to artists and clients. In the 'contacts' table I have created a field 'contact type' that holds data on what type of contact (artist or client) that the record is concerning - Is this necessary? I do not have a contacts table in my relationship graph but rather two table occurences of the 'contacts' table, one called 'Artists' and one called 'Clients'. Would this work fine or am I barking up the wrong tree?

Another thing is when I create a field in the 'contacts' table that is a calculation I am asked which table the calculation should be evaluated in the context of. As I don't have a 'contact' table in my relationship graph my options are Artist or Client. Does it matter which I choose? This I can't get my head around! An example would be concatenating the firstname and surname to make a name.

Thanks in advance again your help is greatly appreciated.

Lee