Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I don't quite understand the relation between Item and ItemCode. Could you describe in pain English? –  invisal Jun 13 '13 at 2:46
    
People have Items that belong to them. Each "People" have multiple "Item" that has information of the Item that belongs to them. ItemCode is the name,code,description of the Item itself. –  ktan Jun 13 '13 at 2:53
add comment

migrated from stackoverflow.com Jun 13 '13 at 7:02

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
    
Thank you for the tips! MySQL Workbench definitely helped me jot down my ideas a lot better. Slow inserts are fine as I'm only inserting details myself and only a couple thousand a day. –  ktan Jun 13 '13 at 3:02
add comment
up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.