linked tables (1 Viewer)

wallacealeco

Registered User.
Local time
Today, 18:46
Joined
Sep 7, 2011
Messages
23
Hi. As an experienced, if very amateur database designer I have been asked to combine several databases that an organisation uses. All the databases are people based so a central file containing this 'person list' seems the way to go. Problem is each user has different needs in terms of what info they want about each person so I could end up with over 100 fields for each person, some of which would never be used by most users. A lot of redundancy! My idea is to have a simple central list to which users are linked. Then each user could have a related table with the extra data they need. My question is would this be better than one large central table.
Thanks in anticipation.
 

AlexHedley

Registered User.
Local time
Today, 18:46
Joined
Aug 28, 2012
Messages
171
Yeah I would create a central Table that has common Fields that all use then create separate Tables with the extra Fields needed. You could create a 1-1 Relationship and only allow certain users to add records to them, therefore they will only be created when necessary.
 

Cronk

Registered User.
Local time
Tomorrow, 05:46
Joined
Jul 4, 2013
Messages
2,770
I don't think this is a black/white situation. My inclination would be to avoid related tables if possible, even with a situation where you had as many as 100 fields with many blank. There is some not trivial work maintaining the one to one related tables if you split the data.

While you are going to have different forms for the different functions, you have to check each time that the person's associated record exists in the particular secondary table, otherwise nothing will display with a one to one inner join, nor be updatable with an outer join.

I completed a system not long ago for the management of forestry plots over a 40 year cycle. I opted for one table to hold data for the eight different treatments, ranging from clearing, planting, etc. I just checked - it has 76 fields, many of which are FK's to lookup tables.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:46
Joined
Feb 19, 2002
Messages
42,971
@Alex/Cronk
otherwise nothing will display with a one to one inner join, nor be updatable with an outer join
Part a is correct part b is incorrect. Why do you think that left or right joins are not updateable?

I'd need to know more about the data to determine if I would use a common table with several auxiliary tables with columns for specific purposes. The first thing to examine is if you have repeating groups and that's why you have so many columns.

If I do go the 1-1 route, I almost always use a tabbed form with subforms so that each related table has a separate subform, usually on a separate tab. Using separate subforms also avoids completely the problem of having to remember to use left joins in the queries.
 

wallacealeco

Registered User.
Local time
Today, 18:46
Joined
Sep 7, 2011
Messages
23
Thanks all for the replies. It's made me think about various aspects. Going down the route of related tables for auxiliary data means that if, in the future a new service uses the database with new requirements I can implement a new related table rather than change the main table.
Some services using this DB want to know every last detail of persons financial, medical, social situation hence the number of fields. In some cases I have chosen to have check boxes to say yes or no to a certain situation rather than choose from a related table. I did this because the form then not only shows what they have got but also what they haven't . I guess there could be a way round this.
Initial trials with related tables seems to work well so I think I'll go that route.
 
Last edited:

Users who are viewing this thread

Top Bottom