Sounds like a fun project.
I'm not entirely understanding what a Berth is, is it the place where you dock your ship? Or the beds in a ship?
Anyway, you seem to be in the right direction with your tables there. The tricky part will be the booking of berths for certain date ranges.
I have a booking system in one of my filemaker solutions but it involves a few checks.
Let's say it's a little bit more advanced than your standard simple contacts database.
I don't know how well you are at working with filemaker and how determined you are.
Because we might be able to help you with little problems if you get stuck, but you will have to build this thing yourself.
About the yacht names, you will have to identify them properly so maybe combine the yacht name with the owner name or something. Because multiple yachts with the same name is just asking for trouble.
PhilModJunk made some excellent contributions in this thread:
It's all about looking for conflicts within a date range. And it's the kind of stuff I ended up using in my Database as well.
But I suggest you start small and start with the basics. :)
This is a private message I got from Alasdair, just posting it here for future reference:
Yes the berth is a physical place where a yacht or boat docks, just like a car parking space!
I'm working on a few files playing around trying to come up with the calculations or scripts to make it work, keeping it as simple as possible. duplicate yacht names can occur but not enough for me to worry about at the moment but I just thought I would mention it.
I can't seem to get this calculation right! when I have a timestamp field, called say DateOut and another called say DateIn I want to have another field which will turn from red (unavailable) to Green (Available)
when the current time and date fall between the out date and in date (Ie when the berth holder is away making his berth free for a visitor).
I hope this makes sense
Thanks again for your help, I have no time scale on this so no hurry!
If you are using a solution similar I would love to have a look at it.
In my file I have a list of equipment that can be rented.
When it's booked it has a starting date and time and a return date and time.
These can be combined to make a timestamp.
Then when someone wants to rent something I enter the beginning date and time and the end date and time.
In my file three Table occurrences with diffrent relationships check for sheduling conflicts. And they basically look up the rental items that have a conflict in this period.
Then I use that list of conflicted rental items to make an *is not equal* relationship with the rental equipment table to show all "non conflicted" items.
And these are the ones you can rent.
I'm thinking your file would do the same.
The three relationships to determine the conflict come from the post from PhilModJunk I provided earlier.
You need to see if:
- Start date of new reservation falls within existing reservation date range
- End date of new reservation falls within existing reservation date range
- Date Range of New reservation encloses an existing reservation date range
What you would need is
- a table containing all your berths. With a Berth ID and possibly information like the berth number, size, etc.
- a table where you enter your bookings. Where you store what "Berth ID" is booked from startdate, starttime untill end date and end time by "boat ID" or "person ID"
And this kind of system can be used for Berths, equipment rental, hotels, motels, etc.
Tell me a little more about the people. You have:
- Berth Holders
- Yacht owners
Is this correct? Or are Berth holders also Yacht owners?
The above sounds great thanks....
Berth Holders are people who own a yacht and pay for an annual berth.
(We allocate a specific berth for them at the beginning of the season 1st of April)
When Berth Holders go off sailing or are lifted out of the water they advise us that they are out of the berth and we can then let it to a Visitor.
Visitors are yacht owners from other locations who visit from time to time. They often return year after year.
Yacht owners come into one of the above two categories.
I can't give you my file, because it's full with loads of other stuff. But I did make you a little demo, example file that shows the technique of booking items and finding conflicting items in a date range:
It's by no means a finished file and requires a lot more details but I think this does nicely demonstrate a few interesting techniques.
Go ahead and try it out. Look at the relationships and scripts and then I'm sure you'll be able to import it into your own solution.
Or alternatively, you start from my file and adapt it to your needs.
I also don't know how you would like to organize all your contacts (Yacht owners, berth owners, visitors, etc.) But I think this file shows a few possibilities.
Visitors aren't really used anywhere, but that's up to you to figure out how you are going to structure those things.