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

MySQLnormalization

MySQL Database

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.

Best Answer

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.

Related Question