normalization key question

[rivet]

I am building a new system ground up.
There are three section ;
- quote
- docket
- invoice

In each section there are three relationships going to the table partner ;
- production (company)
- agency
- bill to

 

table : partner
fields: id, id_company, company, address, phone, contact, email

quote turns into a docket and docket will then have related quotes and invoices.
Once the partners are established in the initial quote, they are unlikely to change in all three section for the duration of the project.

Regarding keys, this is what I am thinking and I am just looking for a 'brain check';
My thought is I want to maintain/relate the initial three partner records across all section unless there needs to be a deviation.

 

In each section I will have three child keys for partner::id:
- id_production
- id_agency
- id_billto

By having a child key for each;
- I can place the fields on layout without a portal
- searches will target exact partnership type
- I believe it will allow for easier modification/deviation of a  partners record

 

Thoughts, pitfalls?

 

 

 

[bcooney]

Have a look at David Graham's "Data Modeling that Scales" video in the Advanced Track from last summer's DevCon. He gives a thorough look at the benefits of the party model.

http://www.filemaker.com/learning/devcon/videos/

Some thoughts I had looking at your post:

1. Will they add more role types? Perhaps a establishing a party_role join table would scale better. party<-party_role->quote with a role_type key in the join table.
2. Why does the partner table have a company field? 
3. Does each partner have one and only one phone, contact*, email?
4. *what is this contact field? 
5. a quote has related quotes?
6. a quote becomes a docket. sounds like a status change and not a new table.

[rivet]

Thanks for reply, I will have a look at the video.

 

1. I do have a role table for unlimited team members ; director, producer, writer etc. But in this case for searches etc I think the fix foreign key is best an still flexible if another partner type does come into play.
2. company name field is for historical reasons.  If the company name changes at some point in time, I need to have an accurate history on past invoices etc. 
3. yes just a single contact and a single email
4. contact field, similar reason as the company field. Also it can be used as an open field that allows any name typed in, with no relationship to any table.
5. yes a quote can have revisions
6. docket. to clarify;  an approved quote will open a new docket in the docket table.

 

[bcooney]

1. How so? What search is served better with a dedicated id_agency field, for example, in the quote table?
2. How does storing a company name in the partner table provide historical (temporal) data in an invoice?
3. A partner has a contact -- relationship to another partner? (btw, D. Graham addresses that in his video)
4. not sure what that does...
5. are you storing each revision so that the user can select from versions of a quote? that can be complex and I would confirm it's necessary. Perhaps capture PDFs of versions and store those?
6. I challenge the need for a separate table for dockets.

[rivet]

1. If I am still on track with you; I have a table occurrence quote_agency ( quote::id_agency = partner::id AND quote_one = partner::type_agency ) so when the client does a search in field quote_agency::company, it will only return partner records that are agencies. (NOTE I forgot to mention the partner fields in initial post; type_agency, type_production, type_billto )
2. What if the company that was invoiced in 2015 has a name change in 2017, What name should show on the 2015 invoice after the change?
3. yes just finished video thanks, and saw that
4. ignore
5. Interesting I have be debating about storing all quote line items as json until the quote is awarded, at which point I would burst into line items.
6. Am I thinking old school on this one?  There can be multiple invoices and quotes on a single project, what binds them, just a docket#?

 

thanks again.

 

Edited November 5, 2017 by rivet

[bcooney]

I really can't advise specifics without knowing a good deal more of the overall data model and workflow. I just wanted to point out that there's options out there for implementing the "party model" in FileMaker. 

I didn't use Graham's method. I have party>party_role<project  (the party_role table also has id_role_type). He collapses party role to dedicated relationships with the use of Table Occurrences like agency to partner by id and constantONE. 

For data that must not change, such as the company name, address on an Invoice, I would set those fields on Invoice creation. So, you would denormalize at the  invoice.

Not familiar with "dockets"..are they like Sales Orders? In my system Projects have multiple Quotes, but each quote simply changes to a Sales Order (it's a status change). Quotes and Sales Orders are the same table (and related to order_lines). Each Sales Order can have multiple Invoices.

Not sure what advantage storing line items as JSON provides. How would they print a quote?

[rivet]

Yes thanks, for showing that video and introducing the party model to me, definitely some good take-aways in it.  I am partly there with two main tables company and profile, which have their related; vendor, client, agency, contact, artist tables etc.  But to go full 'party model', I will be curious to see how he pulls it off in FM. 

Yes denormalize at the invoice.
Yes Docket is same as project.

The verdict is out on the advantages but if I did do it, I would have a server-side script bursts the json into a table for printing.  The quote system is already like that. Whenever the user edits a quote, a server-side script creates 300 records/template rows and populates the quoted items in about 3 sec, its not that bad. So I was only toying with the idea of not having all those un-awarded, never to be reported, line items, weigh down the system.

thanks again.