I have a table full of about 600,000 people. I have a list of additional information that if we know for any person, we would like to track such as what school they went to, or if they have requested information from us, or what their occupation is. The list of this extra information we would like to track is fairly extensive and the percentage of people we know any one item of extra information for will be very small in comparison to the entire people table. There will also be little overlap of the people we know extra information. For example, of the say 20 extra fields we want to track, we will probably know at most about 5 of those fields for any 1 person. My question is what is the optimal way to set this up given the following scenario: I will want to run queries asking for people that have certain data in one of the extra fields, but does not have any information listed for another field. In the results, I want to know ALL the extra information we have for that person. The different ways I know of to do this and the problems associated with each are listed: 1) Create in extra table with a field for each piece of extra information being tracked. - PROBLEM I have a lot of fields and only a small percentage of these fields will be known for any one person so I'm scared it will waste space. 2)Create a "Group" table that tracks the Person, the "Field" being tracked, and the "Field" data. PROBLEM - I don't know an efficient way to run the queries I am trying to run. Did a cross-tab but had performance problems. 3)Create a different table for each extra piece of information being tracked and Track the PersonID as well as the data for that "Field". PROBLEM - Never done this before. Seems like I would have to join all tables in the query to get all the extra information on one person. Are there other ways to do this? Which is best? I can provide example data if needed. There were a few bidders that suggested option 2 which is the way I initially structured my database. Those that suggested this did not address my point about the queries I will be running and only used a basic 1 criteria query in their suggestion. They did not address my query problem as stated above. Whoever provides the best solution will get the money plus a tip depending on how much better the solution is. I will also send a few more dollars to those providing the next best solutions to compensate for the time you put in to looking at this request.
Answer to question with explanation as to why it is best option and if it requires a complex query, and example.
Microsoft Access Would later like to port to MySql so if that has a big effect on the answer please explain.