MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram

Question

Here is my database design. I've been doing a lot of reading and still don't really know what I'm doing. Hoping you guys had tips!

• My DB will have millions of entries.
• Many "People" rows will have null values below idCounty and some rows will be half null.
• DB is written to once, never updated. Read many times and mostly queried through exact idPeople

Questions

• To make first,middle,last name searchable, should I make them Primary Keys?
• How would I allow to search through ItemCode.codeSection and return all "People" that match?
• 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?
• There will be separate lists for different counties, should I create a View for this?

Any tips that would make this better in this scenario are welcome. Please keep in mind that I'm not doing any updates or changes to rows, just the initial record creation and later lookup.

Answer 1

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.

Answer 2

Regarding your questions:

• first, middle, and last name should not be made primary keys. Consider placing indexes on these fields to enable better query performance when querying on them.
• To query people that match a certain item code, I'm not sure you can do that just yet. See my comment on the relationship between item and itemCode below
• I think that you have the People-to-Item relationship correct. It's one-to-many, 1 person to many items
• I'm not sure whether you will need a view for counties - why do you think so?

Here are some additional thoughts:

• Don't go overboard with the addition of indexes. Though indexes can speed up query performance, they also slow down row inserts. Only index what you must.
• Consider naming all of your tables in the singular form. So rather than "People", you would have "Person".
• I think that the relationship between People and County is backwards. 1 person has one county, but one county has many persons (people).
• The relationship between Item and ItemCode seems to be backwards as well. Are there many items for a given item code? If so, then what you're showing in the diagram needs to be reversed. If you reverse this, then you will effectively have a many-to-many relationship between people and itemCode. Does that sound right to you?

Keep working at it! Becoming a good database designer takes time and lots of practice. You will learn more with each project you do.