David McAfee
davidmcafee at gmail.com
Thu Mar 17 12:31:43 CDT 2011
That might have been me, I gave it to several people many years ago. :) I tend to do this: tblAddress --------------- AddrID (PK) LocationName (Main Office...) Addr1 (line 1) Addr2 (line 2) Addr3 (line 3) faddr (Where I store any foreign/unhandled city, St, Zip) ZipID (Int, FK) AddrTypeID (int, FK) AddrNotes CountryID (intFK) entryDate entryUserID tblAddrZipList ------------------ ZipID (Int, PK) ZipCode (I only store the 5 digit zip, if +4 is needed, I'd store it up in tblAddress) City DefaultCity (Boolean) State (probably better idea to store StateID) TerritoryID (if needed, can also place in tblAddress if not Zipcode based) tblAdressType -------------- AddrTypeID (PK) AddrType (BillTo, ShipTo, Both...) tblAddressContactMethod (Junction Table) ------------------------ AddrCtcID (Int PK) ctcMethodID (Int FK) ctcInfo (619-555-1212, SomeGuy at Gmail.com, www.SomeAddress.com) entryDate entryUserID tblContactMethod ------------------------- ctcMethodID (Int PK) ContactMethod (Phone, Fax, Email, Website, Cell#, Some new technology that hasn't been invented yet) DisplayOrder entryDate entryUserID I usually use junction tables between tblCompany and tblAddress as a company can have multiple address and an address can have multiple businesses running out of it. I also keep all companies in one table, whether they are a customer, prospect, vendor, distributor. A simple flag/FKid can tell me what they are. I do the same with contacts and ContactMethod (as above with addresses) . HTH David On Thu, Mar 17, 2011 at 5:49 AM, Rocky Smolin <rockysmolin at bchacc.com>wrote: > I have a zip code table - can't remember where I got it - that I use in > several apps - the user can enter a zip code and the city and state are > retrieved from the zip code table. > > R > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart > McLachlan > Sent: Thursday, March 17, 2011 4:40 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] DB Design Question - too much normalization? > > For ease of maintenance, sInce the address is directly related to the > entity, I'd put them in the entity tables. As long as you keep the address > fields standard, you can always use a UNION query to get all addresses if > you need to combine then, with a flag to identify the type of entity > > Select "C" Addres,City... from tblCompanies union Select "S" Addres,City... > from tblSocieties union Select "P" Addres,City... from tblPeople > > I also agree with Darryl, I'd try to use some form of Postcode,City, State > lookup table and only store an FK to that tree, rathe than full details of > the geographical hierarchy > > -- > Stuart > > > On 16 Mar 2011 at 21:37, Rocky Smolin wrote: > > > Different entities - one is a list of state accounting societies, one > > is a list of companies, the third are people/participants with their > > home address - even though in most cases the participant record would > > also have a FK to the company table. So the address fields would be > > common to all three but then the other fields describing each entity > > would be different. > > > > Rocky > > > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jack > > drawbridge Sent: Wednesday, March 16, 2011 9:31 PM To: Access > > Developers discussion and problem solving Subject: Re: [AccessD] DB > > Design Question - too much normalization? > > > > Rocky, > > Just curious, but why 3 tables? > > jack > > > > On Thu, Mar 17, 2011 at 12:16 AM, Rocky Smolin > > <rockysmolin at bchacc.com>wrote: > > > > > Dear List: > > > > > > I'm putting together an app for a client that will have three tables > > > that will have address information. There will probably be no > > > overlap. > > > > > > Normally I would put address, city, state, zip, main phone, main > > > fax, etc., fields into each table. > > > > > > Is there any reason to make an "address" table with an autonumber PK > > > and an FK to the address table in each of the other three tables? > > > > > > > > > > > > MTIA > > > > > > > > > Rocky Smolin > > > > > > Beach Access Software > > > > > > 858-259-4334 > > > > > > Skype: rocky.smolin > > > > > > www.e-z-mrp.com <http://www.e-z-mrp.com/> > > > > > > www.bchacc.com <http://www.bchacc.com/> > > > > > > > > > > > > > > > > > > -- > > > 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 > > > > -- > > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >