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

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.

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.

StackExchange Database Administrators Q#44438, answer score: 2