log3

Noobie needs help on 1. design (calendar like database?!)

Discussion created by log3 on Feb 3, 2017
Latest reply on Feb 5, 2017 by siplus

Hello Filemaker community!

 

I started developing my first database with FM14 and I don't know if Im going in the right direction with my concept.

 

Lets imagine I have a car-leasing company. The deal is, that a car can only be leased for a period of years, atleast 1 year, or more.

 

I have a table for customer 'accounts' that holds the basic customer infos, name, adress, etc.

I have a 'car' table holding all the info on my cars (model, color, mileage etc.).

I have 'leasing' tables that hold the infos of who leased which car and when (basically where the data from the 2 previous tables comes together).

I also have an 'Overview' table which is mainly used to display all those data in an organized matter in 1 single layout.

The thing is, I want to display who is leasing a car this year, and also who leased it the previous year and who will lease the car the next year.

 

So basically, I created an Overview-layout with 3 tabs to switch between the years so I can search for a car and see who leased it last year, next year etc.

My approach was to create 3 different leasing tables, one for each year (leas-prev, leas-current, leas-next) The records in those tables have a unique ID which incorporates the year, i.e. car001-2016 and relates to the car and accounts tables.

So the idea is that I have 1 record for each car in my Overview-layout and then I would be able to switch between the years by clicking on the corresponding tab.

 

My questions (concerns) now:

 

1. Is this a 'good' approach by seperating the leasing tables by year? (I don't want to use portals and I don't know how else I would display multiple records of the leasing infos in my overview.)

2. I had to create 3 different overview-layouts, one for each year, looking the same except the year they are currently displaying. Thus I have to maintain 3 layouts and any changes to the layout I have to do on all 3. Is there a better way? (Maybe a script which changes the displayed data when a different year is chosen?)

3. Is there any way, I could automate the creation of a new leasing table once a new year hast started? For example, once 2017 is over and 2018 starts the system would scrap/archive the not needed 2016 table, rename the 2017 table to "leas-prev", the 2018 one to "lease-current" and maybe copy the 2018 table to "lease-next" but changes year to 2019 and deletes the leaser-data.

 

I'm thinking there must be an easier way to this. Basically it's like your own calender-database, except that its split by years since I don't want to bloat the database by adding every year from 2016-2050 in advance.

 

Thanks for your help and comments,

hope I did this right, its my first post here

Outcomes