How do i model a sole trader within a party model?

[tally]

FSDatabase.odb
EDIT: example file here, heavily under construction. It is a draft that i am now working through rectifying errors. only has a ‘person’ form for use as an example until i get problems with the tables sorted.

Hi, sorry to further bother you all.

Im having difficulty understanding how to model sole traders within a generic party data model.

For tax reasons in the UK they are treat as a person, but often use a trading name. They do not have business registration numbers and do not collect VAT at invoice (so no registration number to store). They may or may not have employees, have a fixed trading premises and/or use their own home address.

I am a service provider [landscaping]. The main purpose of the database is to track customer details, manage projects and generate invoices. When their business is to be invoiced, they ask for their trading name present alongside their full name. Their trading name must be omitted when invoiced as a person (private work for the individual). Sometimes i use their services too, and am a sole trader myself (simplifies taxes).

Most examples i find of the party model have a table for ‘Party’ in a 1:many or 1:1 with ‘Organisation’ and ‘Person’. Sometimes also with a ‘OrganisationPerson’ table linking people with an organisation. Generally the ‘Person’ table will include any fixed attributes of a person (name, gender, etc), while the ‘Organisation’ table seems to ‘generally’ include name, VAT registration and business reg values.

So far i can see few options and potential pitfalls, so am looking for views and perhaps some links to examples of how to handle this.

1. Treat as an organisation, but
   without any values for VAT/Business
   registration fields. This can then be
   attached to a person under a ‘Company
   Person’ table, preventing duplicate
   personal details.

• perhaps bad practice to expect empty values so frequently in the
  Business/Vat reg fields?

2. Provision attaching a trading name
   to a person or party.

• I am unsure how to omit this at invoice time if they are having a
  ‘private’ job done as a person (unless i create two different invoice ‘reports’). Im
  only at a basic level with queries but hope to learn more in time. My best guess is maybe its possible to do with the right function/condition and a flag to mark the invoice as ‘private’ (a boolean field perhaps)

3. simply make a table for sole
   traders (like Organisation & Person), and treat as it’s own party

• would mean storing that customers information twice, but would keep them
  as separate parties at invoice time.

Thanks in advance for any advice given, all the best.

[tally]

Example file added.

[Ratslinger]

At the risk of sounding like a broken record -

No code

Use sub forms

Do not try to do everything all at once

Do not clutter forms

sample ---- FreshStart.odb

Needs a bit of polishing but it is effective.

[tally]

This is exactly what i needed! Bit of an eye opener seeing how you went about things with the forms. Thank you very much for this, really good of you…

[gkick]

Hi,

While models are just that, models, the design of your database is ultimately driven by the way YOUR business operates.

As I understand from the context of your post, you are developing an invoicing application or perhaps your custom CRM with statistics etc…

From what is understood some of your customers are natural persons, companies but some are both.
The fact that you may use the services of a customer has no bearing on your database unless you produce a full blown accounting package.

So you could treat your customers as an entity which has one ore more billing addresses, where the billing address table allows you to send an invoice to Joe Blogs or his company Blogs Pty Ltd

Both Joe and Company can have either distinct or share the same address.

As such in the invoice form you would select the entity, then its child Joe or otherwise…

As such your table structure may be something like

customer – bill to (one to many relationship)

reports would show or hide detail conditionally if there is no business rego data in the relevant record.

[tally]

You have understood the use correctly, invoicing with some degree of crm functionality. depends how far i can take that with the embedded firebird database and libreoffice base functionality (i only need single user for now, but may split the file later if needed). I had mentioned using a clients services as i had thought to leave option for cash based accounting in future, but its unlikely.

[tally]

To clarify your answer, may i check i have this correct:

 Customer - BillTo (1 to many)
 Party - BillTo (1 to many)
 Address - BillTo (1 to many) 
 Customer - Invoice (1 to many)

Effectively using BillTo to link a party and address with a customer entity. I would then link that entity to an invoice.

[gkick]

Well, I do not see a need for party, instead I would just have entity(your customer)-billto(customers branch)-job(order)

Now, here we are talking about design not related to the how to within LibreOffice topics, so you may want to do more research on google on the topic of relational database design or simply follow the flow of your business process.

[tally]

Understood. May i ask, is party model more for larger enterprises? I have spent weeks reading in my spare time, but most articles / forum threads i find talk about the party model as being ‘the way to go’. it seems to be at the core of many vertabelo articles for instance. so im now in a loop after feedback on this site. Originally i built a set of tables based on customer and supplier entities, but switched to the party model under the impression it was proper. Finding info more applicable to smaller business has proven difficult to say the least! If im understanding correctly now, the whole party model thing is wrong for a small business. Many thanks for your feedback