How to Build the Best Database 1

[wonderwhy]

Hello!

I have been using Access for several years and I have learned that there is always more than one way to do everything. I have a new database to create and I want to set it up the best way possible. The database will be used to track correspondence with many people at many companies. There will also be other tables/forms to track different projects which will involve the companies and related people.

Often, I have created separate tables for separate types of people and or companies (such as a table for physicians and a table for students and a table for volunteers, table for hospitals, table for research companies). I am wondering if anyone has an opinion about creating one table for all types of people (with a lookup for 'people type') and a related table of phone numbers (so you can enter as many phone numbers of different types for each person). I would also have a Company table and then an intermediary table that associated all related people with each company. My plan is to have a Company form, and a People form. The related people would be added in a subform on the Company form (just a dropdown based on the people table) but their complete information would be stored in the People table and entered into a separate form (that could be accessed from the Company table).

Has anyone run into problems with this very simplistic way of setting up a database? Or successes?

Thank you for your input.

Julia

 

[jsteph]

In my opinion, you'd want a Companies Table and a People, or 'Contacts' table.

I worked on a db where the architect had this idea of having a single table, with an 'entity' that could be a person, place, or thing. It's a school of thought, but in the real world it doesn't lend itself well to application design.

But you definitely want all the 'types' of contacts (physicians, volonteers, students, etc) together, and all Companies as well.

The crux is a 3rd table that has just the PK of each table, which links Contacts to Companies in a many/many way, ie, John Doe may work at 2 companies, and of course one companie will have many People.

--Jsteph

 

[wonderwhy]

jsteph,

Thanks for your input. I certainly wasn't thinking about one table for all people, places and things in one table; just having one table for all people, another table for all companies and a table to stores the primary keys from each thus creating the relationship between the two tables.

Julia

 

[wonderwhy]

jsteph,

Thanks for your input. I certainly wasn't thinking about one table for all people, places and things; just having one table for all people, another table for all companies and another table to store the primary keys from each thus creating the relationship between the two tables.

Julia

 

[JeremyNYC]

Julia,

There's tons of great stuff you can read on the topic--it's called normalization. One great article is Paul Litwin's "Fundamentals of Relational Database Design". I recommend it so often that I've got a copy on my website. But if you google around for normalization you'll find a trememdous amount of info out there.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[BSman]

If the data is basically the same, then put it in one table and include one (or more, if necessary) columns to identify the segregating factor.

I recently helped someone redesign a database to track computer inventory and got it down to 4 tables. One listed people, one locations, one the different items of equipment (i.e., Dell model xxx computer, HP model yyy printer, etc.). The equipment table included a column to identify the type of equipment (printer, workstation, monitor, etc.).

The hardware table listed who had what equipment. It had an ID column and also included a "master ID" column. The master ID was equal to the ID for workstations (CPU) or where the hardware was not part of a "system" (like a CPU, monitor, and printer). Queries made it possible to produce reports, etc., that showed all of the components (printer, monitor, etc.) that were part of each system.

As far as the phone number (and similar) separate tables: This decision is affected by what you can expect. It can be helpful to ask users (repetitively, since it usually doesn't come out at first), what exceptions might occur to the "normal" situation. The important part of the answer to listen to is, for example, "Everyone has no more than a work phone, a home phone, and a cell phone.....except for Clem who has three cell phones, two work phones, four home phones, and also a pager number." You normally have to design around the exceptions, so in this case a separate table would be required.

Separate tables for the same data can help in looking up that data, since (for telephone numbers, for example), you have one column for the phone number and a second for the description of the phone number (home, office, cell, etc.).

With databases typically having variable record lengths, there is not so much of the concern that used to occur where if each phone took 10 bytes and you allowed for 5 phone numbers in each record, then each record would use 50 bytes for phone numbers even if there was only one phone number. A separate table only has a record for each actual phone number. While each record must include a pointer to the record in the master table, you still will probably use less space.

The one problem with all of the separate tables is that it can get trickier making reports, etc., to show all of the data.

 

[lespaul]

Jeremy,

That Fundamentals of Relational Database Design is AWESOME! I'll be sure to recommend that in the future! Have a star!

Leslie

 

[JeremyNYC]

Leslie,

Thanks for the star. Too bad you can't give one to Paul Litwin, the guy who wrote the article.

It really is a great explanation of how to approach relational databases.

Cheers, and enjoy the work.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[LittleSmudge]

20 years ago telex was a popular commercial communications system - try to find anyone still using telex now !

about 15 years ago ( when I was in a field sales role ) I can remember my area mamager getting us to 'find out if your customers have one of those new fax machine things'

10 years ago mobile phone were an extreme rarity, now - almost everyone has one.

So now the Fax has come ( and almost ) gone too.
Pagers are on the decline as more and more people get mobiles.

So who can predict the next technological innovation that your users will need to store a 'contact point' for.

For this reason, all databases that I'm developing now have a separate tblPhone table containing fields:-

MainDataTableRef ( Foreign Key )
PhoneNo, Text(20)
Description, Text(20)

Then using a subForm on the Main Data Form the user can have as many (or as few) 'numbers' as they need.

tblPhoneDescription has a single field Description which provides a lookup facility to save typing.
Typically contains things like
Business DID
Business Swb
Business Fax
Home
Home Fax
Mobile
Co. Mobile
Personal Mobile
Pager


'ope-that-'elps.







G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[JeremyNYC]

That's similar to how I generally do things but instead of using a description field I use a PhoneTypeID field, keyed to tblPhoneType, which lists the possible types. It's easy enough to add a new type, and restricting the user to a list makes the data sortable and filterable in meaningful ways.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[BSman]

Whatever the item is that I'm dealing with in the database, I use a separate lookup table in cases where I may want to include more than just the description. For example, (using phones), you might have in the lookup table a column for the description (ex: fax, mobile, office, home, vacation) and a second column to group different types of phone together (ex: fax and office might be business, while the others might be personal).

If you're dealing with a limited number of changes to the lookup data, then even a table with just the description column is often the best solution.

One consideration in favor of the lookup tables is that if it's decided to change the description of something (for example, "cell phone" to "mobile phone&quot, all you have to do is change it in the lookup table once, and from then on the new description will appear everywhere it is referenced.

There is an alternate solution you might want to consider in some cases. If you are working with a field in which there will be a lot of common data, but new text may be needed somewhat frequently, another option is to use a combo box for data entry into that field where the source for the combo box is a grouping query on that field in the source table for the form. The differences are that you do not restrict data to the list (in other words, what's already in the table), and you make sure that the combo box is "requeried" when it gets focus (so new data added to the table will be included in the list). Using this method will help reduce text entry errors while providing easy entry of new text.

 

[JeremyNYC]

True, though a slightly more sophisticated way to go about it is to use the OnNotInList event to confirm that the user wants to add something to the list. There's great code to do this in the Access [version number] Developer's handbook.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[wonderwhy]

Wow. Thank you jsteph, JeremyNYC, LittleSmudge, BSman. I certainly got the answer that I was looking for (and more). I also found the information from Paul Litwin's article very helpful.

Thank you all!

Julia

 

[willir]

I got to get my 2 cents worth in. I loved LittleSmudges lead in. A lot of wisdom. (Which I guess is why he has just a few stars under his belt.)

I have found it best to create a many-to-many relationship for phone numbers and emails -- and use the same table. In this day and age, a person will have many ways of being contacted -- some have email, some don't; some have cell phones, some don't.

Next, you do not know what new technology will be available. For example - pagers became text pagers became two-way pagers. The description "pager" became obsolete very quickly.

There are some very bright "stars" who have posted to this thread so I wont bore everyone on the the many to many intermidiate table.

My phone table (yep, have to change the name - you can tell how long ago I came up with this table) is

PhoneID
PhoneType - phone, cell, fax, numeric pager, 2way pager, email, etc
PhoneNo - text for phone number, pager, email address, etc.
PhonePIN
Comments - call after hours, privite listing, emergency only, etc.

When I bring up a contact, I have all their info displayed in a continuous form. And I don't have to specifically code for phone number and email addresses on the form, or ponder how to handle the odd ball person -- if the person has more than one email address, more than one fax, and such.


I use a similar approach for addresses. Large compaies will have more than one address. People may have more than one address. And many people can have the same address -- families residing at the same house, employees working at the same location. Again, I code the AddressType and include a comment field, and the rest follows normal conventions for an address.

I query a contact, and have their address or addresses. I query a company, and again, have all their information. I dont have to worry about TelCo Texas, TelCo New York, etc.


I have found by looking at things that initially appear to have a 1:1 relationship as a M:M relationship has added a lot of flexibility, and has made adminsitration of the data much easier.

Richard