rockysmolin at bchacc.com
rockysmolin at bchacc.com
Mon Aug 20 22:26:13 CDT 2007
I had planned to add a Roles table and have a RoleID FK in the Persons table. Limits the role to 1 so many-to-many would be more flexible. I'll have to ask the client for an executive decision. Rocky Original Message: ----------------- From: Charlotte Foust cfoust at infostatsystems.com Date: Mon, 20 Aug 2007 13:04:46 -0700 To: accessd at databaseadvisors.com Subject: Re: [AccessD] Names in tables - best practices That's my experience. However, what are you going to do about the same person having multiple roles? (Never happen, right? HAH) That might better be covered by having a persons table holding the minimal basic info on the person and a join table (something like PersonRole) with the person ID and the Role ID. That would allow you to have the same person hold multiple roles but would eliminate having to maintain multiple records for that person. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of rockysmolin at bchacc.com Sent: Monday, August 20, 2007 12:05 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Names in tables - best practices Thanks for that, Charlotte. I thought it would be better not tocombine them at this point, but I see what you're saying and the bet is that upfront cost of redesigning the db wil probably be offset by simplicity of maintenance? Original Message: ----------------- From: Charlotte Foust cfoust at infostatsystems.com Date: Mon, 20 Aug 2007 08:22:46 -0700 To: accessd at databaseadvisors.com Subject: Re: [AccessD] Names in tables - best practices Definitely combine the tables. Maintenance is much simpler. If some of the data collected is radically different for different roles, put the common fields in the persons table and use one-to-one joins to tables containing the unique data collected for that particular role. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at Beach Access Software Sent: Saturday, August 18, 2007 4:38 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Names in tables - best practices Dear List: I have a legacy app in which the client is considering making a big change. There are currently threes table which have names in them - employees of the user (a law firm), employees of the user's clients (many to many person to client), and foreign agents. There may be more in the future - vendors, for example. What we are considering is consolidating the three tables into a general 'Persons' table and adding a field for 'role' - containing a FK to a Role table so we can add more roles as time goes on. Is this a good idea? The roles don't really overlap. And there are fields which are unique to each of the three tables. Right now I don't think there are any duplicated names among the three tables. So one role per name should suffice. Any opinion welcome. MTIA Rocky -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -------------------------------------------------------------------- mail2web.com - Enhanced email for the mobile individual based on Microsoft(r) Exchange - http://link.mail2web.com/Personal/EnhancedEmail -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -------------------------------------------------------------------- mail2web LIVE – Free email based on Microsoft® Exchange technology - http://link.mail2web.com/LIVE