0

I've been reading up on proper DB creation techniques, and I've got a large project that will have numerous items in it. I have already mapped out the tables and the various lookup tables, but then I realized that in the user table, I have first_name, middle_name, last_name columns that could be changed to first_name_id, middle_name_id and last_name_id that act as lookups to a first name table, middle name table and last name table that hold only unique names to prevent duplication of data.

The question I have is how far do I go with this process? Does every single item that could potentially be duplicate information need to be done like this? At some point this seems like it will get confusing to ME to keep track of all the relationships and cascading updates/deletes, etc on everything...

Just looking for some advice as I want to make sure this is done properly as setting a proper foundation is very important to build and scale on top of in the future, but at the same time don't want to take this to extremes if it is not needed.

MattE
  • 1,044
  • 1
  • 14
  • 34
  • You should avoid the temptation of over engineering your platform. – John Cappelletti Nov 10 '18 at 14:34
  • I would advise not to do this. you might save a miniscule amount of storage but it will add complexity to all your queries. See also [Is normalizing a person's name going too far?](https://stackoverflow.com/a/782551/73226) – Martin Smith Nov 10 '18 at 17:00
  • To answer the initial question... The answer is to use a unique constraint. That said, unless you have a compelling reason to do so, don't normalize names. The name "Matt" isn't a distinct entity in the way Order_ID: 854123 is a distinct entity. Think of it this way, if something about "Matt" were to change, would you want that change propagated to every single person with that Name_ID: Matt attribute? – Jason A. Long Nov 11 '18 at 03:18

0 Answers0