TOPIC: Better Design
Better Design 4 Years, 4 Months ago

I have to store the details of the community served by the hospital. The people belong to two categories - employees (of the factory served by the hospital) and their family members. The detals relevant to an employee are their name, gender, sex and service details like service number, section, date of retiring, address... The data significant for a family member are name, gender, service number of the employee (no other servcie details) and address. All persons are given a hospital number (generated by the program) and an employee can be identified from the hospital number as it ends in -00. All these details should be in one table as i use a keyfield based on hospital number to display the information from this table in various other tables. What are the options I have?
1) Employee information and information common to all members linked to the employee in one table and the individual information of different family members in another. This way how can i use the keyfield (to derive from 2 tables) - using query union ? Presently I am using Trigger to output the employee information (except those common for all members) on to the table that stores the family members information. Thus few fields are stored of the employee (~ 5 fields 4000 records) are stored in both the tables. This way the key field is deriving from only one table. But the data entry screen is split up into two.

2) Use only one table with all fields for both employees and family members, modify the access using dynamic menu for the two category and thus leave some fields empty for family memebers (~ 10 fields, 12000 records).
3) Any other way?

Which is better?
Re:Better Design 4 Years, 4 Months ago
Hi Ajith,

I would be tempted to use solution 2, just for simplicity's sake. As long as there is some way to distinguish employees from family members. You might also want a field to link the family member to the employee.

I wouldn't worry about having empty fields in the database table. Fields in the database are not stored with a fixed length, so empty fields should not cause a problem with database size.
Jeff Ferguson
Suneido Software
Re:Better Design 4 Years, 4 Months ago
Hello Jeff,
Thanks for the advice.