Help with relational database needed

[Blagmeister]

Please excuse the newbie dimwittedness but I have an idea for a database but am not sure where to start.

I have already constructed a flat database for my business which keeps records of student records, contracts and licence expiries etc and I have to say I am chuffed to bits with FM as it is saving me loads of time and trouble.

However, I also look after a pal's website [www.masnoell.com] together with the associated internet bookings which currently arrive via email, and I am convinced that FM is the way to go rather than the method currently used which is using 3rd Party online forms, html pages to show availability and excel spreadsheets to keep a track of the bookings!

In a nutshell he has 6 properties which he rents out over the course of a 9 month season. Each property has a different rental rate dependent on both the property and also the week of the year it is to be let [to take account of seasonal variations in revenue]. The properties are let out in weekly periods or multiples thereof and the changeover days vary from property to property.

My plan is to attempt to construct a database which I can host on his webserver which will function as not only an online booking system for his clientele, [they can enquire after properties by entering dates and the database will advise on availability and cost etc based on their input] but will also enable us to maintain a livetime database of what properties are available and which are already let together with client details and revenue summaries.

Only problem is, not really being familiar with the relational aspect of databases, I dunno where to begin!!!

If anyone can point me in the right direction as to where I should be going [in idiots terms please!] I would be most grateful.

Many thanks

Blag.

[NYPoke]

As with anything, get yourself organized. From the Relational standpoint, you need to organize your data. Best rule is that you never have your data in two places. In other words, you have a Client Table, containing their Name, Address & such. You then reference that info though a Unique Client Id, generated by FileMaker. Don't let your Client Name exist in multiple tables. Same goes for most data.

You need to look at tables for Properties, Clients, Pricing, Bookings, Billing, Payments, plus a few others probably. You can then group them into files. You probably want files accessed by outsiders (The Web) in one or two files, financials in another, and maybe a file for some general information.

The details are rarely clear the first time you put it together. So, put together high level design (or test DB) & see if it makes sense. Then, add more fields & data as the process becomes more clear.

In version 7, unlike previous versions of FileMaker, you want to layout your data (tables & fields) first, then let your Layouts & Scripts build on top of them. Geeks generally call this a Data Driven System.

Work really hard on organizing the Tables & Fields, then the Layouts & Scripts & Web Access will be much easier to work with.

[Fenton]

If I were you I'd start by building the FileMaker database, then think about how to get it on the web. From a basic FileMaker point of view, which is to first see what "entities" (a geeky word for "things") you have. First, you have a Properties table, one record per property.

Then you have a RentalsRates table. Now, I don't know how these rates are established, so I can't say exactly how it would be done. Is it a calculation? Such as: certain months have a different rate, which could be calculated as a percentage of a base rate for that property? Or is that each week has a rate, which is not really calculated so much as just entered?

In the monthly case you'd have 12 records per property, hence 72 records. Or possibly not even a Rates table, just a calculation in the Rentals table (later).

In the weekly case, you would have 52 records per property, 312 records. Kind of a lot, but fine if they don't change often. Still, it would be better to have a property base rate and a calculation by week of year. Because FileMaker has a function, WeekofYear, which could be used to calculate this. In which case you'd only need 52 records; or a big calculation in the Rentals table. But, as I said, I don't know whether this is the case.

As you may have noticed, the structure to get the rates depends entirely on how the rates are arrived at. If it is not wholly calculated, then it would be the addition, Sum(), of the rates of however many months or weeks were booked.

You need a Rentals table. This would a record per rental, with the PropertyID, the start date and the end date. The dates would then yield the start WeekOfYear and end WeekofYear. These could be used in a "compound" relationship to the Rates table.

Stop: There is no point in going further until we know how the rates are arrived at.

[Blagmeister]

Thanks very much indeed gents. Some very much appreciated advice on what was initially a very daunting prospect. I have actually started to build a DB starting with client info as that is pretty much what I have done before and I am comfortable with that. When it came time to start looking at the bookings/rates issue, I figured that I would need to be working in other tables which is a new step for me.

As far as I can make out, I need to take into account that the properties actually fall into two clear groups for which there is a pricing structure for each, based on the period of the year.

To complicate matters further, dependent upon the property, the "changeover" day varies from a Thurs, Friday or Saturday, so the transition between each rate band is not quite as clear cut as first it appears.

This is the rates breakdown in simple terms.

Now, I don't know if I am barking up the wrong tree here but as you suggested NYPoke, I have set things up so that each enquiry produces a new record with its own unique ref generated by FM. I am now trying to get my head around how I should create tables for each property and how these will relate to the pricing bands. I was considering using relational tables by referencing the booking "weeks" with a number and then relating that number to a price band. That way if a punter books one or more "units" for that property relating to a given week reference, FM can compute for me the price per unit or multiples thereof. [At least I am confident that it can!]

Whether this is the most logical way to go about this I doubt very much, but until I have had a bitmore of a play around as suggested, its about all I can manage cerebrally!

I do appreciate your help gents and I will keep you posted on developments. Perhaps if I ever get this to work as I intend, I can share it in the forum in some way!

Cheers

Blag.

[Fenton]

It looks like to me that you're going to have to use WeekOfYear, with a calculation in each property to adjust when the week starts, using DayOfWeek.

You could either do a record for each week for each property (lots). Then you can use a compound relationship, using PropertyID=PropertyID, and =><= operators on the Weeks.

Or you could use a calculation or Custom Function (requires FileMaker Developer) to create a multi-line calculation field in the Rates, so you could have 1 record per "period," with multiple lines for the weeks in each (with the PropertyID also). You would do the same in Rentals.

Then you'd have 2 multi-line fields facing each other in the relationship, and could do a Sum() of the costs.

ID Week

01 25

01 26

01 27

You can see how 2 of these could match each other in a relationship.

Either way would work. The first, with a record per week per property is easier to understand, but more tedious to enter. Depends how many weeks are in a "period."

[NYPoke]

Read Fenton's last post carefully. Good stuff to think about.

I'll give you two more "suggestions" that will likely save you grief in the future:

1) Don't build your scheduling Data Table based on Weeks. Build it on Days (unless your friend wants to run a Brothel, then build it on hours - joke). The Scheduling Table would have a Start Date & End Date, plus a Client Id, a Property Id & maybe a Down Payment/Payment field to say it is Reserved.

Your Scripts would initially default to selling Weeks, but you could adjust it in the future without needing to retool your Data Table (pain in the arse).

Yours Scripts & Layouts would have to be smart enough to look and see the Start & End of the Weeks (Mon-Sun or whatever), then you write an entry in the Scheduling Table for that week. In the future, you could break it down by days.

2) Create a Billing Transation Table. You are going to have Clients pay & cancel, pay then add or remove weeks. The data entry will have mistakes. All sorts of things can go wrong.

You want to be able to do basic Credit/Debit transactions, without a lot of trouble. So, the Billing Transaction table would have an unique id for itself & a id related to the Reservation/Booking Table. You can then charge them 375 pounds (Euro?), then credit them 75, then charge a late fee of 35 - whatever. It all totals up nice & clean.

3) A BONUS SUGGESTION: In your pricing bands (Pricing Table), make sure you have a Unique Id there. BUT, create a second table - Pricing History. Each time you make a change to the Pricing Table, you move the new data to a NEW record in the Pricing History Table.

Thus, your pricing bands/Pricing Table gives Current Pricing. But all your quotes & billing come off the Pricing History Table - which has its own Unique Id. (The id for the latest entry in the History Table is kept in the pricing bands, so that you can assign it to Quotes, Invoices & such.) You can now track your pricing over time.

This process saves enormous amounts of grief when dealing with pricing. Very effecient billing process.

[Blagmeister]

Once again gents I am indebted to you both. You have given me loads to go away and think about, and indeed to research as a majority of what you suggested went straight over my head! [My earlier comment relating to "idiots terminology" being the issue here!]

I think my first objective is to become much more familiar with both the calculation elements of FM and the relational aspect of the program's capabilities as I am still very much a n00b in these regards. Until I have more of a handle on these I may well be peeing in the wind, so to speak.

I do have FM Dev Fenton, so I will having a look at the Custom Fucntion in due course and NYPoke, your suggestion that my pal run a brothel is marginally more attractive an idea than his current set up! [More work intensive for me though ]

Having now got a shopping list of things to go and learn about before I can start moving this much further forwards, I will away to my manuals and will keep you posted of any developments. [No doubt I will be badgering you for help when I start sinking in the quicksand of my limited intelligence!]

Thanks again guys.

Blag

[Blagmeister]

One thing you may be able to clarify for me Fenton - I am in the process of creating tables and thus far have one for Client Data, one for Price bands [of which there are 10] and another for each property.

The bit I am having trouble getting my head around is that of the dates issue. The properties are rented in weekly chunks or multiples of same with there being 3 "Arrival" days across the 6 properties, dependent upon the property.

Both Apartments start on a Thurs

Cottage and Barn on a Fri

Dairyhouse and Workshop on a Sat.

There is a 227 days period total over which all the properties are rented [the "Season" if you will], and I am now stumped as to how to go about creating a dates table.

Any [laymans terms] suggestions?

[Fenton]

I don't see that the arrival dates matter. It's just something for the data entry person to be aware of. Thurs, Fri, and Sat are all in the same week (well all days are in the same week :-).

There is a Custom Function included, ExplodedKey ( ID; Start; End ), to produce the multi-line key in the Rentals table. I modified it from its original, which used dates. We're just using numbers, WeekOfYear. Hopefully that will be enough.

RentalRates.zip