Noob: Relationship trouble

[frinholp]

Hi All

I am new to creating databases and using filemaker.

I am creating a database for a shop scenario.

I have created a product table which has a field Category.

I have created a table for category as I would like to add new categories in the future.

Within the category table I have created a field CategoryName and linked CategoryName in the category table to Category in the product table.

This has created a many-to-many relationship, but a product can only have one category and categories can have more than one product. I want this to be a one-to-many relationship.

How do I go about this?

Thanks in advance

[bruceR]

"This has created a many-to-many relationship"

What makes you think that? It would probably be better to base this on CategoryID if you ever rename your categories. But aside from that, what you have set up should already be doing exactly what you want.

[Vaughan]

A many-to-many relationship has a "join" table* between the two entities: the classic example is the invoicing system where there is a m2m relationship between invoices and products, and the "line items" table is the join file:

customers -> invoices -> line items <- products

You don't have a join file so it cannot be a many-to-many.

* It is possible to create a many-to-many using multi-keys (multiple values in the primary or foreign key fields) so technically a join table is not always needed, but it's usually preferable.

[comment]

If you want Filemaker to SHOW that the relationship is one-to-many in the relationship graph, then set the CategoryName field in the Categories table to validate as unique.

Even better, use a CategoryID field, defined as auto-entered serial number in in the Categories table, as the matchfield - as mentioned above, this will allow you to rename a category without breaking the relationship.

[frinholp]

Thanks Everyone for your quick response.

"What makes you think that?"

It was the crows feet in the relationship graph.

"You don't have a join file so it cannot be a many-to-many."

"If you want Filemaker to SHOW that the relationship is one-to-many in the relationship graph, then set the CategoryName field in the Categories table to validate as unique."

I was wondering how Filemaker can make that assumption?: Silly me!

All the rest of my tables I have created I used an auto-entered serial that I was using as the primary key. I thought I would use CategoryName as the unique identifier as that field would be unique. Never thought of validating it to be unique. Doh!

Cheers for the advise on using CategoryID. Though I cant see me renaming a catagory, on the off-chance I was to, this would help data consistency. Thankyou for pointing out my shortcomings.

Thanks again

[Vaughan]

"Though I cant see me renaming a catagory..."

You'll see the humour in this one day. :

The trick with primary keys is that they have to be unique and invariant. Names of things are often neither. The only way to ensure that the keys are unique and invariant is to use something like a serial number.

It also helps to add a third criteria -- the key contains no information -- to the requirements because this avoids problems with the key representing the year it was entered or the invoice number or something.

[comment]

Not to nitpick, but "key is invariant" and "key contains no information" are synonymous.

[LaRetta]

Not to quibble but I believe Vaughan is correct in using both phrases.

Invariant has several meanings but, in this context, suggests that the key is 'unchanged by specified mathematical or physical operations or transformations'. However, 'contains no information' implies that the key has no external meaning, such as month created or invoice number.

So the key should remain unchanged (invariant) and be meaningless (contain no information). I believe both phrases are important descriptors here. :wink2: