One to One relationship

[iNovice]

I realize that a one to one relationship is almost non existent, but I believe one is applicable in my situation, but I'm not sure how to set it up properly.

I have defined 2 tables.

Businesses: (The complete list of business names to solicit potential customers)

Customers: (A customers list derived from the business name list who become customers .) One business = one customer.

The only way I can get the relationship graph to recognize the relationship as a one to one relationship is to create a business ID primary key serial number for the business table, and a business ID primary key serial number for the customers table. Both serial numbers are defined the same.

Is this properly setup for a one to one relationship, and if so how would be the best approach to maintain database integrity when creating a customer record. Would I create the new customer record from within the business record? If I create a new customer record from the customer table it does so in the order of the serial number rather than relating to the Business record ID that I need.

[Genx]

... Wouldnt you just put the business details and the customer details within the same table... i mean arent they the same thing in this case?

[Genx]

Furthermore, the relationship graph registers only relationships, it doesnt really have anyway to represent if they're one to many, many to one one to one, many to many etc. Its all shown exactly the same way, it is the way you utilize the relationships that makes that relationship one to one, one to many etc. and the way you utilize them is completely up to you.

~Genx

Edited May 20, 2006 by Guest
Edit...

[iNovice]

Thanks for the response.

The database will eventually be web based. The business records will only be used for occasional direct mail marketing. The real activity of the database, (finds indexing etc), would take place in the customer table.

I thought general performance would be greatly enhanced when searching through a thousand customer records versus searching through hundreds of thousands of business records to find a thousand customer records.

[iNovice]

Are you saying that if I created a business ID field in the customer table representing a foreign key relating to the primary key business ID field in the business table, and even though the relationship graph indicates I have a one to many relationship, I still have a one to one relationship based on the manner in which I create the layouts?

[Genx]

The relationship graph indicates no such thing unless you have made your fields (primary and foreign unique on either side). All it indicates is that the two are related by those particular fields... unless you specify your foreign key to be unique (there is however, in your situation no REAL purpose in doing this as all the key's will be generated automatically).

Hope this helps, Welcome to the forums and I love the name!

~Genx

[Genx]

Yes, your relationship types (one-to-many etc) are based more on your layout elements (i.e. if you turned on allow creation of new records and placed a portal on your layout, this would allow one to many relationship), if you instead however simply put a set of related fields directly on that same layout...well then you would have the capacity for a one-to-one relationship.

I had a good example but i've forgotten it now, sorry, anywho, i've got a plane to catch (yes im proud) so i'll let someone else handle this over the next couple of days.

Last thing though, filemaker find's are slightly more complex and better engineered than you seem to think. You can have 1000 fields in a table, but if you only enter data into one during a find request, only that one field will be searched across.. I personally don't delve a lot into this area but comment or Ender or someone might be able to help out here, they seem to be good with performance related issues.

~Genx

[Ender]

One-to-one relationships are rare, but I do agree that this looks like a good place to use one. Usually, the relationship graph will show the relationship as a one-to-many (where the 'one' is on the primary key side and the 'many' is on the foreign key side). This is because FileMaker shows keys that are auto-enter serials as a single line on the relationship graph, and shows the chicken foot for other relationships (like those involving a foreign or alternate key). While you can also get this single line for keys that have global storage and for fields that are validated to be Unique, neither of these are particularly useful here.

I'd recommend having the primary key be an auto-entered serial Business ID field in the Business table, and then put the foreign key be the Business ID field in the Customer table. The foreign key should NOT be an auto-entered serial, but instead get it's value from the Business record it's associated with.

There are a variety of ways to create the Customer record and populate its Business ID field. You could grab the ID from the Business record, and insert it into a new Customer record. Or an easier way is to have the "allow creation of records in this table via this relationship" option on the Customer side of the relationship, and then from a layout based on Business, use a Set Field[] script step to set a value in the related Customer record.

[iNovice]

Thank you for the suggestions. I will delve into it and see what I come up with.

[Genx]

Oh right oops, so you can have non business customers... never even thought of that.

[comment]

Contrary to what Genx thinks, I know very litle about performance issues. However, this method has many potentional pitfalls that should be pointed out - if only because they need to addressed.

The problem with keeping a duplicate subset of a larger table is synchronization. There's no way that Filemaker will do this for you, so many processes must be tightly controlled. Creating the child record is the easy part. What happens when the record needs to be edited? You must decide WHERE the edit should happen, you must ensure that the edit is propagated to the other table, and you must prevent the user from editing data in any other way. Another issue is deleting a record: it must be made clear whether a record is being deleted from the subset only, or is it purged entirely from the database.

This requires careful planning of the user interface and privileges, so that there's no way for the user to create discrepancies between the two sets.

[iNovice]

No, all customers would come from the business database.

[iNovice]

But if this is a one to one relationship isn't it essentially 2 parent records, or at least as I understand it, parent equals the one side of the relationship and the child typically represents the many side of the relationship. What happens if they are both one sided and will always be one sided.

The problem I'm facing is that I want to be able to delete a customer record without deleting the related business record. The only thing I can think of is possibly creating an (inactive customer) field where I can control the customer fields layout without having to delete the entire record, while preserving my business fields layout. Or am I drifting into deeper waters.

[comment]

Yes, it is a one-to-one relationship, but it isn't symmetrical. One record, the child, "knows" who its parent is. The parent's ID is written in the record's ParentID field (foreign key). The parent, OTOH, holds no information about the child. The child can exist or not, there can even be several children - but this information is not stored anywhere in the parent record. IOW, a one-to-one relationship is a one-to-many which happens to have only one in the many.

You should be able to delete a customer record without deleting the related business record, without a problem. You shouldn't be able to do the opposite. All these things are doable, they just need to be more tightly controlled in your setup. This means restricting user privileges so that the only way to do these things is by scripts that you have provided.

[iNovice]

I tried doing a set field script but it's not working.

I created an add customer button in the business table layout that I had hoped would allow me to create a customer record. I would first perform a find and locate the business record via a business ID primary key field search. After finding the matching record, I would click the add customer button which hopfully would take me to the customer layout, create a new record, insert the Business ID into the related Business ID field, (foreign key), and then go to the first Customer data input field establishing a related customer record.

The following script is the one I'm trying, but no business ID is being set in the Business ID field of the Customer table layout. I do get to the Customer layout and it goes to the first field assigned with the blinking insertion point, but no ID is being inserted into the business ID field on the customer side. Any clues to what I'm doing wrong?

Go to Layout (Customer)

New Record/Request

Set Field (CUSTOMER::business_id; BUSINESS::business_id)

Go to Field (CUSTOMER:: name_first)

[Ender]

I mentioned a couple possibilities, but you seem to have mixed them up. Try either of these:

1. The "grab the ID" approach:

#From BUSINESS

Set Variable [ $businessID ; BUSINESS::business_id ]

Go to Layout [ Customer ]

New Record/Request

Set Field [ CUSTOMER::business_id; $businessID ]

Commit Record/Request []

Go to Field [ CUSTOMER:: name_first ]

2. Create it via the relationship approach:

In the relationship definition, set the option on the Customer side to "allow creation of records in this table via this relationship", then use a script like this:

#From BUSINESS

Set Field [ CUSTOMER::someField ; someValue ]

Commit Record/Request []

Go to Related Record [ CUSTOMER ; using layout: Customer ]

Go to Field [ CUSTOMER:: name_first ]

With the first method, there's an opportunity to add multiple Customers for each Business (this could be avoided, if you wanted.) With the second method, if a Customer already exists, it would be overwritten by the new one (this could also be avoided, if you wanted.)

Edited May 25, 2006 by Guest

[iNovice]

Thank you! The second method you suggested seems to work as I had hoped. I can create a new customer record from a button in the business table taking me to the customer table and inserting the business ID on the customer side.

I then dragged the customer ID and the customer status fields over from the customer table into the business table so that I will be able to determine whether the business is a customer based on whether they have a customer ID.

[Ender]

Glad it's working for you.

Edited May 26, 2006 by Guest