My job is to create a database using Filemaker Pro Advanced 11. I've started the modeling phase, creating the ERD diagram using SQLEditor. The ERD is attached.
Please take a look at the attached ERD and post some comments on what you think. I want to make everything right from the start, writing the names correctly, making the right relations and so on. Every kind of criticism of what is done, and ideas of other ways of doing - please help?
Database description and questions:
The database is a member-database. Every member has to pay an annual fee. The actual members in each year are registered in the "Member_registration" table. Also the persons can be invited to different events. The invited guests, and their current status (attending/not attending) are registered in the "Event_registration" table.
The members each own a number of apartments. The apartments are time-share apartments, which means an owner "owns" the apartment a given period (a week) once every year. A single time-share can be for sale. Also a single apartment can be for rent at a specific year. The renter then can be another person in the "Persons" table.
The database should support up to 500 members and 2000 apartments. In the beginning, it be made to support only one user, but later on I will need to bring it online, letting a website pull information, as well as letting a few other users accessing the database. In the end, my hope is to let every member view and edit their own information online. But keep in mind, the first step is just to make a stand-alone database for one user to use on a single computer.
- I need to expand this setup to support sending out custom emails to specific groups of persons as well as newsletters. Will the current setup support me doing this? Or what tables will I have to create?
- In the "For_rent" table, I have made two fields the primary key. What I mean is, the combination of an apartment and a given year have to be unique, and will therefore work as a key. Will this work in Filemaker? Will it be better to make another field and make it the primary key? How can I make sure, a given apartment are not for rent more than once a given year?
- In the tables, I only use "k", primary key, and "kf", foreign key. What about "surrogate key", what is that, and when will I have to use it?
- Do I have to choose which fields to "index"? In that case, which fields should be indexed?
I am NOT an expert! I've read some articles, and uses the "Filemaker Pro 11 - The Missing Manual" book to get started. Also I have created some simple MS Access databases through time, so I have some experience.
Kind regards, Raahaugen