Encerrado

Optimal table structure

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.

## Deliverables

Answer to question with explanation as to why it is best option and if it requires a complex query, and example.

## Platform

Microsoft Access Would later like to port to MySql so if that has a big effect on the answer please explain.

Habilidades: Engenharia, Microsoft Access, MySQL, PHP, Arquitetura de software, Teste de Software

Ver mais: data structure needed, would dollars, data structure explain, get dollars, tables data structure, table data structure, set data structure, question data structure, money table, list data structure, much money engineering, much engineering get, explain data structure, data structure solution, data structure set, data structure problems solutions, data structure problems, data structure problem, data structure list, data structure example, best data structure, basic data structure, answer tab, answer query, want know engineering

Acerca do Empregador:
( 93 comentários ) Brandon, United States

ID do Projeto: #3015060

13 freelancers estão ofertando em média $9 para este trabalho

oscommercevw

See private message.

$4.25 USD in 3 dias
(137 Comentários)
5.6
preyasvw

See private message.

$12.75 USD in 3 dias
(34 Comentários)
4.4
itsoftvw

See private message.

$12.75 USD in 3 dias
(16 Comentários)
3.8
felenkavw

See private message.

$6.8 USD in 3 dias
(26 Comentários)
3.4
computercity

See private message.

$10.2 USD in 3 dias
(5 Comentários)
3.2
olgakvw

See private message.

$3.4 USD in 3 dias
(6 Comentários)
1.9
zambio

See private message.

$12.75 USD in 3 dias
(1 Comentário)
0.9
cgelhaus

See private message.

$12.75 USD in 3 dias
(0 Comentários)
0.0
ikrakow

See private message.

$12.75 USD in 3 dias
(0 Comentários)
0.0
hemaldesaivw

See private message.

$8.5 USD in 3 dias
(0 Comentários)
0.0
vw1031922vw

See private message.

$8.5 USD in 3 dias
(0 Comentários)
0.0
Gorgeous01

See private message.

$8.5 USD in 3 dias
(0 Comentários)
0.0
aq

See private message.

$8.5 USD in 3 dias
(0 Comentários)
0.2