Home > Designing and creating databases > Creating a database > About planning a database
A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.Follow these general steps to plan a database:
Relational Database Design 101 (part 1 of 3): Designing a Flat File Database
Relational Database Design 101 (part 2 of 3)
Relational Database Design 101 (part 3 of 3)
MY STANDARD LIST OF FILEMAKER LINKSThe White Paper for FMP Novices is useful -
Filemaker Free - Listing of free resources -
Free unlocked templates
By Richard Carlton Consulting
Starting Point -
RCC Blog -
By yzysoft.com Contacts | Products | Invoices | Letters
Sample Database -
A free calendar is available and can be integrated into your database
Seedcode Calendar Free -
Filemaker's Online Help Pages
FMP 12 http://www.filemaker.com/12help/index.html
FMP 11 http://www.filemaker.com/11help/index.html
FMP 9 http://www.filemaker.com/9help/index.html
FMP business database demos - some are fully useable
The Excelisys Business Tracker V3.0 -
Filemaker released a FMP runtime database in 2007 for students called "Campus Productivity Kit" and is available online, notably at CNet Contacts | Meetings | ToDo List | Links | Notes/Files
Google "Campus Productivity Kit" https://www.google.com/search?q="Campus+Productivity+Kit"
WIN - http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577579.html/
Mac - http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577567.html/
You can rename the database from a .usr file to a .fp7 file and open in FMP 7 thru 11
Lynda dot com has Filemaker Videos (portions are free)
YouTube - Filemaker Videos
UK FileMaker Channel
FileMaker, Inc. Channel
Lynda.com Channel - Filemaker videos
Dwayn Wright's Channel
Soliant Consulting Channel
Filemaker Academy Channel
Skeleton Key Channel
Accelerate Computer Training Channel
Filemaker NewsScoop.it is an information curation site - free option available.Filemaker Info - http://scoop.it/t/filemaker-info
Filemaker News - http://www.scoop.it/t/fm-news
Filemaker Wikipedia -
Filemaker - Version By Version -
Search specific Filemaker sites with this Custom Google Search
"Each Client has Personel. The personel may be at one or many of the locations so the relationship of personel to locations is one to many"
If each personnel will belong to one client, and each personnel is only at one location, that's fine. But if a personnel can cover several locations you will need a join table between Personnel and Locations.
"The personal traing records, now that I am writing this, is a one to one relationship as each person has only one training record" - I doubt that is a flexible long-term view. Surely personnel will accummulate a training record for basic this, advanced that, certified the other. At any stage you will want to see all the personnel with basic training in this, and due for renewal, etc. I would create a Training Record Table.
"the training record fields can go under the Personnel table and reduce my tables by one entity" - I think saving one table is a false economy. I would say always create one table too many that one too few.
"The relationship of Medical director to client is a one to one relationship, which would mean based on the tutorials, that I should make the Medical Direction a Field on the client Table but I want to compile the basic contact info for the Medical Director so I think this should be a seperate table." If one client has only one medical director (at a time) then that is correct.
"I will like to be able to have the corresponding client documents archived" - my view is never create an archive file unless there is some really big driving reason for it. You will create the extra work of the archiving process, maintaining two compatible tables, run the risk of mis-match between records that are 'live' and 'archived', and at some point I guarantee you will want to look at data that doesn't care whether the records are 'old' or not - probably even wanting to see them on one report. There probably are examples of where an archive table is worth it, but I have never used one. Simply have a field 'ArchivedYesNo', or 'ActiveYesNo' and tag it as appropriate. Portals, searches, relationships etc can all be made to ignore the 'Archived' records so users are not bothered with them day-to-day, but can access them immediately when they do want to see them.
David- TY for all of the links and information. There is a tremendous amount of inforamtion there. I wish I had all the time to go through it all! I have booked marked all of it and will slowly start to digest it all. As I said I wish I had the time to read it all and then start on my DB. The reality is I will need to build it and learn as I go, which for me has always worked better via trial and error, than reading an inordinate amount of information and then trying to remeber what was said and how to apply it.
With that said, I wanted to get input on my daabase design. From what I read this is a critical part of the DB design which is why I posted my diagram here, to get the feedback on this initial stage.
"If each personnel will belong to one client, and each personnel is only at one location, that's fine. But if a personnel can cover several locations you will need a join table between Personnel and Locations." I will make them a join table. What do I cal this table?
, to reply to your comments about the Training Records and Personell...I would agree with your thinking that there usually are a list of trainings andf certifications to track, hence the Join Table idea.In my instance I only have to track one certification, CPR. For each person, I need to have the following data concering their training, Date of Class, Name of Instructor, Name of training facility, date of expiration, Course affiliation ( AHA or NSC). Basic Personell data to be kept is: Name, address, emil, cell, department they work for and location.
So it is possible to keep all of this in one table or do you still think I should have a Training Table and use that as a join table between Personell and what? I will want to generate a report and an email from this so I can notify a supervisor and the person with an email of their coming expiration date. For example I will want to generate a report that lists all expiring personell in the next 90 days and send them email and calendar notifications. With this being the case, it looks like I have a one to one relationship f personnel and training record. Each personnel has only one record for me to maintain.
Both Dave and Sorb, thank you for taking the time to read and reply to my post. I appreciate the effort.