1

So I am working on my DB design skills, and have tried to create a normalized MySQL database. Here is the diagram:

enter image description here

  • The reason I added the Address table is to break the address fields into separate columns, making the geography more searchable (i.e. search for zipcode).
  • Employer to Post will be a 1 (employer) to many (post(s))
  • My idea for position, since there will be a set number of positions would be to seed a table (Position) and store the position id in the Post table
  • Location works under the same premise of Address on the Employer table, however it may vary from the address on the employer table, earning it it's own table.
  • Finally, Industry will be seeded just like the Position table.

I would appreciate constructive criticism, as I am still working on becoming better at my schema design.

  • When two tables have the some column structure (as Location and Address do here) That is almost always an indication that they should be the same table (conceptually and logically, even if separated physically for performance reasons). – Pieter Geerkens Oct 14 '14 at 2:28
  • So ask yourself, "What is the design assumption that leads me to think that Address and Location are separate entities rather than the same one?" – Pieter Geerkens Oct 14 '14 at 2:29
  • Well, the structure is the same, however Address would be the primary address, where location could be different depending on Post.. So I am not sure, to be honest.. Can I have a "primary" address in the Address table? – Chris Oct 14 '14 at 2:34
  • The relationship between Employer and Address is 1-N; but the relationship between Position and Location is 1-1. Therefore, each Address must have a FK to the Employer, and each Position must have a FK to the Address, with a transitive relationship to the Employer via the Address table. – Pieter Geerkens Oct 14 '14 at 2:38
1

When two tables have the some column structure (as Location and Address do here) that is almost always an indication that they should be the same table (conceptually and logically, even if separated physically for performance reasons).

So ask yourself, "What is the design assumption that leads me to think that Address and Location are separate entities rather than the same one?"

Answer:
The relationship between Employer and Address is 1-N; but the relationship between Post and Location is 1-1. Therefore, each Address must have a FK to the Employer, and each Post must have a FK to the Address, with a transitive relationship to the Employer via the Address table.

  • 1
    Do you mean "Location" or "Position"? – Chris Oct 14 '14 at 2:41
  • @Chris" Post actually - corrected now – Pieter Geerkens Oct 14 '14 at 2:47
  • In other words, a Post is related to a particular Employer by virtue of being at an Address belonging to that Employer. – Pieter Geerkens Oct 14 '14 at 2:49
  • to ensure that I am fully understanding your explaination, I have revised my diagram: imgur.com/95nnz1r Now with the updated setup, the "primary" address would be the Address key on Employer and the "Location" address would be the key on Post? – Chris Oct 14 '14 at 2:57
  • @Chris: Two points: 1) The transitive closure relation between Post and Employer should go, as it is a denormalization. 2) You should put cardinality markers on your relationships, as they fundamentally affect the meaning of your diagram. Use whatever notation you are comfortable with, but do something. – Pieter Geerkens Oct 14 '14 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.