Table and Relationship structure for records with 450 fields

[SummitSeeker]

This is my first post on the forum so Hello World...!!!

I have questions about table and relationship structure for a couple specific types of information and One-to-One vs One-to-Many relationships



I am a realtor designing a db to keep records of my business...

If I want enter information about all my client's offers how should my tables be structured for this...??? There will be around 100 fields worth of information... Should I just make one large table for all the fields or should I break it up into many tables...??? It seems to be that I would want just make one big table because all 100 fields of data will be for one record and that record won't change... I know you are supposed to try to normalize the data but this is an area I struggle with and since the data I import regarding an offer will not change I'm thinking it would be okay not to normalize...

Thanks for any tips you can give... This is a big part of my db so I want to make sure I get it right...!!!

[Dal Jeanis]

The purpose of normalizing the data is to break it up so that related information appears in a single record, and so that repeating information is separated, not duplicated, and not leaving big gaps full of nothing.

For example, You wouldn't put the customer's name, address, phone and contact information on each offer. You'd put that all on a customer table, and link the customer record to the offer record by its customer key, a single field. You wouldn't put all the information about the property on the offer either - that would be on a property record, and the property record key would be all that was needed on the offer.

To save yourself a lot of grief, take a quick trip over to Access MVP Roger Carlson's site at http://www.rogersaccesslibrary.com/ and review his tutorials on database design. He has samples of a technique called "Entity Relationship Diagramming". Do a couple of samples, then take a stab at your own design. For about 2-3 hours spent understanding the concept, you'll save yourself ten times that much time in problems avoided later.

[SummitSeeker]

Hey Dal thanks so much for the reply... I do have most of my db structured like your examples so I guess my question is how far do I take normalization...??? For instance my Offers table... I may have up to 100 fields of data to enter a complete offer and answer all the questions it takes to place an offer... Will it be okay to put all 100 fields in an Offers table or will I need to break it down? I know that normalization says break it down so what will happen if I just use one table...??? What are the major consequences I need to be aware of...??? I will have a few foreign keys like customer_ID, Property_ID, etc, but the rest of the fields will be offer specific...

Thanks again for the help... I cannot express how badly I need the tips...!!!

[Dal Jeanis]

The first rule of database design is "know your data". The second rule is "know your workflow".

A table should contain the information regarding one "entity". If that entity is an Offer, and all 100 fields of data are data about the offer, not about the person making the offer or the property the offer is on or the bank that has preapproved the offer, then they all belong in one table, unless there's a good technical reason to break it down. For instance, there might be some subset of data that will be updated repeatedly, and that you want to keep history on. If so, then put that data on a separate table. In your case, you will probably regard any change as a new offer, so that wouldn't be needed.

Even if something really belongs on a separate table, there are valid design considerations other than normalization. For instance, it is very common practice to put telephone numbers (home, cell, pager, fax) directly in the customer record, rather than having them in a separate table with a phone type. If you won't have many customers who have three cell phones, that might be just fine. It saves making queries with complicated joins.

Normalization is a yardstick, not a straight jacket. Even big mainframe shops - maybe especially big mainframe shops - will denormalize databases to the degree necessary to support access speed, minimize resource usage, and so on.

In Access applications, the most important consideration is that you understand how the data will be used, and plan wisely to make things easy on yourself. You have to support the thing, so don't use any methods that you don't understand fully.

Try this yardstick on for size - what questions do you want to be able to ask the database, and do you know how to ask them if you put it all in one table?

[orange]

It is very, very rare to have a well designed database with a table containing 450 fields. As Dal suggested research Normalization and work through some of the tutorials on Roger Carlson's site--- especially http://www.rogersaccesslibrary.com/T...lationship.zip