Beginner Help: Table structure in database

[island]

Hi,

I am a healthcare provider having interest in filemaker.

I am using filemaker 12 pro. First time user. So pls excuse.



I want to build a solution which maintains health reports of employees.

Each employee has a distinct employee number and some characteristics like place of working, and general data like name, age sex, etc

Than there will be yearly tests say test1, 2, 3,.....

Multiple years like 2013, 14

I want to make a solution that can maintain and compare yearwise data.

I should be able to create reports showing - employee data of particular year of all tests, comparative yearwise data of specific test, comarative data of employees.

I have thought of following structure of table: employee no as joining key

1.employee data - emp no, name, age, sex, ....

2. Year 2013 with tests1, 2. 3,

3.Year 2014 with tests1, 2. 3,

is is ok? or a better way?

I appreciate your effort in reading this post and pointing me to right direction.

I'd be grateful if anyone has a similar solution or template ready.

Thanks again.

[eos]

I have thought of following structure of table: employee no as joining key

 

Don't use a key that has meaning outside your database, is outside of your control and is not guaranteed to be unique; instead, use auto-generated keys to define your relationships. You can still use your company-internal employee no for display purposes. 

 

Also, the structure should at least contain two tables:

 

• Employees (primary EmpID, emp no etc.)

• Tests (primary TestID, foreign EmpID, year, #, date, result)

 

and maybe more tables, depending on the nature of the tests and/or results.

 

This way you have one single table with the test data where you can filter and sort records by a variety of fields or combinations of fields - year, employee, test result etc. – and create reports based on that data.

[Agnes Riley]

I would probably set up 3 tables. One for the person, one for the tests and one to connect the test with the person. The third one is called a join table.

You can call the third one people_test_join and that should have the foreign ID of both the person and the test.

Thus way all the information about the test can be stored in the test table and the join table will contain when the test was taken for a particular individual and what the results are, etc.

[Fitch]

Agreed. The year is an attribute (i.e., a field or derived from the date field) of the test. It should not be an entity (table).

 

Welcome to the forums.