mac | pc Structuring tables (ERD) question

[reeprutger]

Hi everyone,

 

I'm  new to Filemaker and have a beginner question about how to structure my tables (ERD'style).

 

Here's the situation:

 

I want to create a database in which I connect three entities:

 

1. Clients

2. Invoices

3. Artworks/Artists 

 

- Clients can have multiple invoices

- Invoices can have multiple artworks (however, every artwork is unique!! unlike generic products)

- Artworks/Artists: every artwork is unique, and one artists can have multiple artkworks

 

*I would like to add a feature in which I can connect multiple pieces of art with multiple clients - I call this "interests". To avoid to creating a many to many relationship I created a separate item for this.

 

In the file I added is a first attempt to create a good relationship model. However, it does not seem to connect properly.

 

Any ideas on how to structure these entities in a good way are more than welcome. Tips, suggestions, and examples too!

 

Thank you,

 

ReepRutger

 

 

[comment]

- Clients can have multiple invoices

- Invoices can have multiple artworks (however, every artwork is unique!! unlike generic products)

- Artworks/Artists: every artwork is unique, and one artists can have multiple artkworks

 

I don't see that you have an Artists table; why is that? Also, can an artwork have more than one artist?

 

Note that an RG (Filemaker's relationship graph) is not the same as an ERD. In an ERD, you can have multiple relationships between two tables; to implement this in an RG, you need to use additional occurrences of at least one of the tables involved.

[reeprutger]

I don't see that you have an Artists table; why is that? Also, can an artwork have more than one artist?

 

Note that an RG (Filemaker's relationship graph) is not the same as an ERD. In an ERD, you can have multiple relationships between two tables; to implement this in an RG, you need to use additional occurrences of at least one of the tables involved.

 

Hi, thank you for replying. 

 

I didn't think it would be necessary to make a separate table for Artists as you can add this as a field in the artwork table. Maybe it would be better to create a separate table for that. But wouldn't that mean more work; having to create accounts for all the artists. 

 

Do you have an idea on how create the interests table? 

 

Thank you.

 

Reep

[eos]

Hi Reep,

 

welcome to the Wonderful World of FileMaker® and this forum.

 

To add to comment's post (I wrote this before, but forgot to post it…):

 

I want to create a database in which I connect three entities:

 

1. Clients

2. Invoices

3. Artworks/Artists 

 

It seems you have more than that, since an Artists ≠ any of his/her Artworks:

 

Artworks/Artists: every artwork is unique, and one artists can have multiple artkworks

 

– Artists

– Artworks

– Invoices

– Clients

 

plus the join/child tables of LineItems and Interests

 

Not sure what you mean by “avoiding creating a many-to-many relationship”. Some relationships are just that, and the “separate item” of Interests that you created will play that role. 

 

Assuming that an artist can create many artworks, but each artwork was created by only one artist:

 

Artists --< Artworks -- LineItems >-- Invoice >-- Clients --< Interests >-- “ArtworksForInterests” (see below)

 

Now you can use the following checklist to see if these relationships fit your requirements:

 

• An Artist can create/have many Artworks, but each Artwork has only one creator (True? If not, you need a joinTable between Artists and Artworks)

• Since an Artwork is a unique object, it can appear on only one Invoice (at least within your business …).

• Each Invoice, on the other hand, can comprise multiple Artworks, which is why we need a LineItems table.

• An Invoice has a (1) Client, but a Client can have multiple Invoices.

• A Client can also have Interests in multiple Artworks, and an Artwork can (simultaneously) be of interest for multiple Clients (a concept that could be extended to something like a reservation system)

 

You can express these relationships as shown in an ERD, but in FileMaker you cannot just drag a new connection to the existing TO (table occurrence) of Artworks, since a path must be unambiguous – you need an additional TO of Artworks to relate it to the Interests table. This new TO is simply another representation of the same Artworks “base table”, but it has a different function than the TO related to Artists/LineItems.

[comment]

I didn't think it would be necessary to make a separate table for Artists as you can add this as a field in the artwork table.

 

I am not sure what data would go into that field - but would that be all you record about an artist? Otherwise you would find yourself repeatedly entering the same data, e.g. artist's phone number, address etc.  So it would actually be much less work (and less errors!) when an artist moves to another address/phone.

 

 

 

Do you have an idea on how create the interests table?

 

Yes, but I need an answer to my (two) questions first.

[comment]

• Since an Artwork is a unique object, it can appear on only one Invoice (at least within your business …).

• Each Invoice, on the other hand, can comprise multiple Artworks, which is why we need a LineItems table.

 

This is a simple one-to-many relationship (each artwork has one invoice only): therefore a line items table is NOT required. The only complication here is that the parent (Invoice) is created after the child (Artwork), so the existing child needs to be "adopted". Sometimes it can be more convenient (in terms of user interface) to add a redundant line items table just for selecting the artworks - but I wouldn't rush there.

[LaRetta]

This is a simple one-to-many relationship (each artwork has one invoice only): therefore a line items table is NOT required. 

 

But what if the Client wants to purchase two art pieces on a single Invoice?

[comment]

But what if the Client wants to purchase two art pieces on a single Invoice?

 

Then you must enter the InvoiceID as the foreign key on two Artworks records, instead of just one.