Link to home
Start Free TrialLog in
Avatar of Raahaugen
RaahaugenFlag for Denmark

asked on

Validating database ERD. Tips and suggestions??

Hello experts,

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?

Other questions
- 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
Avatar of challengeday
Flag of United States of America image

Hello Raahaugen!

Good job on your ERD.

This isn't an exhaustive review, just some things I noticed:

* I've learned to use __kp_ for primary key and _kf_ for foreign key. This way when fields are sorted by name they rise to the top. The two underscores before__kp puts it above _kf.

* I would in general give join tables (like For_Rent), their own own primary key (__kp_For_Rent_ID). I find it keeps things clean and straight. You can validate unique combinations with a separate field.

* I would change kf_renter to kf_person, to be clear (it's understood that the person is the renter)

* In your For_rent table it looks as if you could still have multiple people assigned to the same apartment/year combination, which isn't what you want.

* To constrain one apartment to one renter for one year, I would create a field called validate_unique_per_year and put an autoenter calculation into it that concatenates the apartment id, person id and year "kf_person & "-" & kf_apartment & "-" rent_year". Then I would validate the field to require a unique value. This way I'd still be able to have the same person and same apartment in that table multiple times (over the years) but the combination will have to be unique.

* You probably want a field in For_rent that specifies the week the person has the property during the year.

* Will it be sufficient to keep only the current sale price for Apartments? Or will you want a history of previous sale prices? If so, the sale fields in Apartments should be broken out into their own table with a foreign key to apartments, and add a date field.

* Sorry, I don't use SQLEditor, can you post what A, P, F, U, A, etc stand for (I can guess but I'd rather be sure)... thanks!

Best of luck!
Avatar of Raahaugen


Thanks for your inputs. I appreciate it!

Starting from the end, here's an overview of the prefixes:
A: Auto increment
U: Unique
P: Primary key
F: Foreign key
?: a note is attached (not visible)
N: Not null
D: The field has a default value (not visible)
i: Indexed

I know not all the fields has been specified with the correct preferences yet. I will go through them all, and as well make some adjustments based on what you suggested. When I finish, I will upload a new ERD.

Kind regards, Raahaugen
Just a few more questions together with the new ERD:

I'm not interested in keeping a history log on sales prices through time. So I will leave that out, unless I am missing something else?

Also, when do I have to index fields?

No prob on sales, I just brought it up as something to think about.

Indexing: Indexing creates a physical index of the data so it is faster access and find, so it can be a tradeoff between size and speed. In my experience I don't worry too much about size and prefer speed. Also in my experience, FileMaker does a pretty good job of indexing what it needs to index (if you do frequent searches on a field, FM will automatically turn indexing on for that field unless you've specifically told it to not index).

My rule of thumb is to always index primary and foreign keys and let FM do what it wants for other fields (set to none with auto create as needed), unless I have a specific reason to have a different setting for a particular field.

New ERD:
* All primary keys should be i,U,A,P.
* Make the use of the naming "_ID" on foreign keys consistent. Either on for all or off for all.
* In For_rent, _kf_apartment_ID should not be A (Auto increment)
* Sorry I missed this the first time: You don't need _kf_person under Apartments (unless you have a specific use of it that is different from renting, or I'm misunderstanding your structure)
* I would put week field in Apartments in the For_rent table.
* Don't know what consordie is
Avatar of North2Alaska
I like the ERD too.  It can help in organizing your thoughts.  But there are a couple of things to think about when you start doing the FM work.  The Table Graph (TG) is NOT an ERD.  FM uses tables (actually Table Occurrences (TO)) differently than any SQL type development you've done before.  For example, FM will not let you build the relationship between the Persons, Apartments and For_rent tables the way you have the defined in your ERD.  Because relationships in FM are bi-directional, you can not create a circler relationship.  The TG is used to identify relationships between TOs.  I think of them as saved searches in a lot of ways.

As far as your primary keys, I would not use Auto Increment, but Auto Enter.  There is a slight but significant distinction.  I would use a Auto Enter calculation that will guarantee a unique key.  I use something like:

// Record number
Right( "00000" & Get(RecordID); 6 ) & "-" &
//Date formated YYYYMMDD
Right ( Year ( Get ( CurrentDate ) ) ; 4 )  & Right ( "00" & Month ( Get ( CurrentDate ) ) ; 2 ) & Right ( "00" & Day ( Get ( CurrentDate ) ) ; 2 ) & "-" & 
// Time formatted HH24MMSS
Right( "00000" & Hour ( Get ( CurrentTime ) ) & Minute ( Get ( CurrentTime ) ) & Seconds ( Get ( CurrentTime ) ); 6 )
I forgot to mention.  I found an interesting site that is trying to suggest standards.  There is some good things there.  And if you don't like all their standards, it does at least give you something to think about and maybe a place to start.  One of the items I liked was the custom function UUID.

Check it out...
Agree that ERD is different from the Table Graph.

I think of Table Occurrences as similar to Table Views even though that's not truly accurate... it's like a virtual table with all of the fields of the original.

North2Alaska, why not use Auto Increment?
A couple of issues I don't like to have to deal with.  1)  As you may have seen in other threads, a number in FM has a max value (don't have it off the top of my head).  So, it can cause problems.  2)  If you need to move data around between files, it can create issues with duplicate keys.  The risks are moderate, but why risk it when a truly unique key can be generated.
Thanks for the new inputs. Again, I really appreciate! And welcome to the thread, North2Alaska.

I have refined the "Table graph" based on the new tips and suggestions. Please take a look. It is really important, that everything is done right in the graph, because the app "SQLEditor" supports FM output, so that all the tables, relations and fields should be created in filemaker without extra work. (We'll see if that works!) So thanks for helping me correcting! :-)


* Sorry I missed this the first time: You don't need _kf_person under Apartments (unless you have a specific use of it that is different from renting, or I'm misunderstanding your structure)
- The apartment has an owner, which is specified in the _kf_person_ID field. Is that wrong?

* I would put week field in Apartments in the For_rent table.
- The for_rent table refers to an apartment, which already has a week field. I'm not sure I understand, why you want the week field in the For_rent table as well?

* Don't know what consordie is
- The apartments can be in one of five different blocks. I call them "Consordie".

- If I'm not to make the circular relationship, then how should I make the relationship?
- You've got a good point regarding the unique ID. I will consider that, but I will have to do it in FM directly.

- I have checked out and renamed the fields in my setup. Helpful page!

I have another question, as it was asked in the original question:

"- 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?"

I hope, you don't mind taking another look at the PDF?

Kind regards, Raahaugen

I've used SQLEditor for creating MySQL databases, but never for FM.  I, too, will be interested in the results.

As far as modeling the relationship, I'm not sure how SQLEditor would handle it.  To avoid circular relationships, FM will create another TO.  You might have a People » For_rent » Apartments set of TOs and a People » Apartments » For_rent set of TOs.  And you won't be able to use the same name twice, so one of the sets will have to be renamed.  There is lots of information about how to use the TG and one method I've come to appreciate is the Anchor/Buoy method.  There's lots of information on the net about how to make this work.  Here's an example from a project I'm currently working on.   Herd.pdfI just don't know how you would model this in SQLEditor.  TG ¿ ERD  :-)
TG does NOT equal ERD.
Avatar of challengeday
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

Regarding the apartment<->person<->forRent relationships. Let me explain: A apartment is always owned by somebody, a person. But the person who rent it is always different from the owner. If the apartment is not out-rentet a year, the database will conclude the actual owner are having the holiday in the apartment.

Again, if you still think, the way I do the relationship is wrong, let me know.

I'm trying to understand the thing about TO - Maybe I should just get started organizing the whole thing in Filemaker, and then it will make sense?
Yes, start building your TG.  SQLEditor is fine for defining tables and columns (usually called "Fields" in FM speak) but that is just a small part of the overall construction of the TG.

You will also find that you will create more fields than you can possible think of up front.  As you begin to design your solutions you will have other calculations, summaries, global fields to build relationships, etc...  So, again, the table and fields are the very tip of the iceberg.
One more thing before I give out points;

I remember that I have read somewhere, that Filemaker integrity control is not as strictly implemented. See below.

Taken from on page 19:

Overall, Referential integrity control is not as strictly implemented in FileMaker. Relationships can easily be broken if you’re not careful. “Special Edition Using FileMaker Pro 5” by Rich Coulombre and Jonathan Price presents an excellent overview of Referential Integrity in FileMaker and how to enforce it. Reflecting I think the FileMaker community’s ability to build very robust solutions.

Does this mean, that even if I require a person, owner, to be related to an apartment, I can't always be sure, that Filemaker keeps this "promise"? What can I do to enforce it no matter what?

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your inputs!! I hope that you can both accept to split the points?

I will start the hard work before long, and will most certainly ask more questions along the way, so follow me if you will on the database questions :)

Also, this is a side-project, so it will be on-off through the next months.

Have a great day /Raahaugen
Hi Raahaugen, I'm happy to split points, we're all helping each other out :)

Thanks for explaining the difference between renter and owner, and yes, then the way you have it is fine. The only time you get into circular relationships is when you are actually building the relationships in FileMaker TG (and it will a. not allow it and b. tell you about it).

I would suggest in this case differentiating the foreign keys, to be id_person~renter and id_person~owner.

I still think that week is an attribute of the renter-apartment combination, no?

Hi Challengeguy,

About the circular relation I will try to build it up as Nort2Alaska proposed. We'll see :)

About the "week". I know it sounds confusing, but actually an apartment is owned a single week only, and every year. So in a year having 52 weeks, it is possible the apartment will have 52 different owners. This is why week is a attribute to apartment.


Kind regards, Raahaugen
Right, but the problem with the way you have it specified is that you can only specify 1 week per apartment (instead of 52). Each row in the Apartment table should be a unique apartment.

This is the way I see it:

Apartment A
Apartment B
Apartment C


Apartment A, Joe, Week 1
Apartment A, John, Week 2
Apartment B, Silvia, Week 1


I get your point. And it is a very good point!

The case is, there are maybe 3.000 apartments, and most of the "owned apartments" are actually not in specific weeks, but "floating weeks" and "floating apartments" as well. The week number are defined from year to year, as the owner "places" his week - and he will be able to choose among whatever apartments in the specific class is still available. So regarding building the database, I define the ownership as owner of one week in one apartment - though the week and apartment aren't always specific.

I know this sounds a bit confusing. The fields in the apartment database are still to be finished. I think I will do it along the way designing the thing within filemaker...
I see. Yes, sometimes you have to get your hands dirty and see how things work. Take it easy and best of luck, let us know how it goes :)