Jump to content

Tables and Relationships


Neil Scrivener
 Share

This topic is 2282 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 by Kris M
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :)?

 

 

Link to comment
Share on other sites

This topic is 2282 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.