Tables and Relationships

[Neil Scrivener]

Hi Guys,

So before making my big Database (and before purchasing V14) I am starting to put together with pen and paper various tables I would like to have. First of many questions!

So, for example, I will have an invoice table, and things such as the customer, amount(s), dates etc will be stored.

As a connected item to my Invoice Table I'd like to have a budgetter within the Database. Basically, what we do is file certain 'types' of expenses, with a total statement per job. So I can see Invoice 123 and find out what expenses were related to that job. 

I've uploaded an example of how we do it in an Excel format currently.

I'm looking for some pointers in terms of how this could transfer to a FM database.

Taking into account the sheets of expense types I have - would I need to have a table for each expense type - and then an overriding tables of expenses - and then on top of that links to my Invoice Number? 

Or is there a simpler more effective way to do this without billions of tables?

 

Cheers

N

Project Budgetter.xls

[Kris M]

https://en.wikipedia.org/wiki/Database_normalization

https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model

Couple of important concepts to understand as you build.

The right design can promote scalability and make future maintenance less painful.

I would also suggest you look at demos of commercially avaialble invoicing solutions, the FM Invoices starter solution, and FM Starting Point from Richard Carlton Consulting for suggestions and inspiration. Its not always necessary to reinvent the wheel

Edited March 29, 2016 by Kris M

[Neil Scrivener]

Thank you Kris, I shall look into these links.

[Fitch]

It looks like you could get by with just a single expense line items table, and add a field for 'type' (vehicle, purchase, etc.). That will make it simple to report on and link expenses by invoice.

[Neil Scrivener]

Thanks Fitch So I could then use an FK for the invoice line.

So FK's

1 - Invoice 123 - Vehicle - £1.00

2 - Invoice 123 - Purchase - £2.50

3 - Invoice 456 - Purchase  - £1.40

I guess then it would be easy to do a sort against Invoice 123 and a sub-sort of Types, which would then present it all very nicely :)?

 

 

[Fitch]

Exactly.