Relationships / Join Fields

[x14607]

I am new to the forum, and to FMPro 7... and to using relationships in databases... I've used older versions of Filemaker though, so am somewhat familiar with it.

Situation:

3 Tables:

Products table (info on each product)

Resources table (contains resources required to produce the product)

ProductResources table (joins the two tables above with fields: Product, Resource, Qty)

If a product requires anything to produce it, I use a portal on my Product layout to enter in the Qty and Resource for each resource needed.

Problem:

I change data in "Product" field in Product table (title of the product)...

Resources in the portal disappear, because ProductResources table still contains the OLD product name.

So, when I change the product name, ProductResources table sees it as a new product, which has no associated resources.

I was hoping to NOT have to make a SKU or any static type "key", because I'll need this concept for ClientIDs, which will be able to change, etc.

PS - Field definitions state they are required to be unique, which is why I want to keep Product as the Key here

Do I have any choices here other than creating a SKU-type Auto-enter serial field?

Any help is greatly appreciated - Thanks in advance.

[RalphL]

It is best not to use data fields as primary keys. I generally use an auto-enter serial number in every table as the primary key. I prohibit modification by the user. The primary key should never change it value.

I have posted a many to many relationship in the Separation Model part of the Forum. Check the latest version. Even if you do not want to use the separtion model I think you will find some useful information.

[transpower]

It doesn't have to be an auto-enter serial number, although that is usually the best method. You could create an ID field, specified as a Unique value and Not empty, for each table and fill it in manually.

[x14607]

I currently do have the ID field (product.. the title of the product which must be unique and not empty)...

The problem though is what I have listed above. I will say this though:

The relationship from Product table to my join table (ProductResources) is a one-to-many, and the relationship from that to the Resources table is also one-to-many.

I thought they should be many-to-many, but cannot find an option to change the relationship type.

I really prefer not to create any kind of serial, maybe for this example I will, but for some other examples within my database... it would just get sort of confusing. It seems as though the data field is what I want to use, but perhaps I'm very wrong here. Again, very new to the relational database concept.

Thanks again in advance for any help.

[transpower]

The join table should have "many" on both sides; in other words, Product to ProductResources should be one-to-many, and Resource to ProductResources should be one-to-many. Each record in ProductResources should be one product and one resource. If you do a subsummary for product in ProductResources you should see all resources required for each product. So something seems to be wrong with your database architecture. If you change the product name in the Product table, then you'll have to change the corresponding field in the ProductResources table. If you have hundreds of records, you could write a script to make the change.

[x14607]

My relationship is exactly as you described it should be, and so the best option it seems is the Serial-type number for any and all relationships. Or atleast something that won't ever change(and is required and unique). (examples: Creation Timestamp, auto-enter serial, etc)

Thanks again for your help