Solved

Validating database ERD. Tips and suggestions??

Posted on 2011-02-13
22
1,466 Views
Last Modified: 2013-11-05
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
Members-database.pdf
0
Comment
Question by:Raahaugen
  • 8
  • 7
  • 7
22 Comments
 
LVL 4

Expert Comment

by:challengeday
ID: 34883922
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!
0
 

Author Comment

by:Raahaugen
ID: 34884167
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
0
 

Author Comment

by:Raahaugen
ID: 34884332
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?

/Raahaugen
Member-database.pdf
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34884502
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
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34885054
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 )
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34885129
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.  

http://filemakerstandards.org/

Check it out...
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34885131
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?
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34885202
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.
0
 

Author Comment

by:Raahaugen
ID: 34885885
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! :-)

Challengeday:

* 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".

North2Alaska:
- 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 filemakerstandards.org 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

 
Member-database.pdf
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34887431
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  :-)
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34887440
TG does NOT equal ERD.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 4

Accepted Solution

by:
challengeday earned 250 total points
ID: 34889894
Hi Raahaugen,

- The apartment has an owner, which is specified in the _kf_person_ID field. Is that wrong?

My underlying question is: What is the rationale for having person id in both For_rent and Apartment? If you are tracking only one relationship (owner/renter) then it should be in only one place.

For emails you can either create a set of keywords you will flag people with (a field in People) or you could create a groups table (and a group_person join table). The keywords field is easier, and gives you enough to do a search on before sending out an email.

I'm curious how the "id" naming scheme will work for you. I like its simplicity (but I'm used to doing it the other way!)
0
 

Author Comment

by:Raahaugen
ID: 34894568
Challengeday:

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?
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34895489
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.
0
 

Author Comment

by:Raahaugen
ID: 34896387
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 http://www.rcconsulting.com/PDF/fm_access_comparison.pdf 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?

/Raahaugen
0
 
LVL 12

Assisted Solution

by:North2Alaska
North2Alaska earned 250 total points
ID: 34896505
First, this quote is about Version 5.  FM had a MAJOR rewrite for version 7 forward and now at version 11.  That said, FM is very flexible and you have full control over referential integrity.  You can enforce it or not, but it will keep it's promise.
0
 

Author Closing Comment

by:Raahaugen
ID: 34896530
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
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34899064
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?

0
 

Author Comment

by:Raahaugen
ID: 34899156
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
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34899282
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:

Apartments
=========
Apartment A
Apartment B
Apartment C

Persons
=======
Joe
John
Silvia

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


:)

0
 

Author Comment

by:Raahaugen
ID: 34899735
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...
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34899790
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 :)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now