You're on the right track. Look closely at your 2NF result - this is where the 3NF result will come from. Your next task would be to take the 2NF result and eliminate the fields that do not depend on the key.
There are numerous sources for how and why to do this, but I prefer Microsoft's explanation the best:
http://support.microsoft.com/kb/283878
To make first,middle,last name searchable, should I make them Primary
Keys?
No, any field in any table is "searchable".
A primary key is a unique identifier of a row in a table. Your idPeople
field satisfies this requirement, though since you stated the table will have millions of rows, you'll want to make that an INT
or BIGINT
. The maximum value of SMALLINT
is 65535. See http://dev.mysql.com/doc/refman/5.0/en/integer-types.html
How would I allow to search through ItemCode.codeSection and return
all "People" that match?
One way would be:
SELECT *
FROM PEOPLE
WHERE idPeople in
(
SELECT idPeople FROM Item WHERE idItem in
(
SELECT idItemCode from ItemCode WHERE codeSection = 'Some Section'
)
)
Another way would be to use JOIN
.
I hope I understand the foreign key concept. It's on the "Item" table
so it allows multiple items per "People". Is this the correct way?
Looks correct to me. The notation is called crows foot
notation, and on the Item
side (1 line and a "crows foot", it's saying 1 to many items. On the People
side (parallel lines), it's saying 1 and only 1 person. Using this notation, some of your other relationships appear incorrect. (People to County, in particular)
There will be separate lists for different counties, should I create a
View for this?
You could, but they could also just be separate SELECT statements or Stored Procedures.
Best Answer
If ResponseToTransactionId is always NULL for items marked 'REQUEST', and contains the 'REQUEST' items ID where the status is 'ALLOW' then It means that from an application point of view you should always be able to locate which records are linked by this field.
And if NULL then you know there is no link.
It is a bit unusual to do it this way, but it should be fine