-1

NOTE: I have never done this before:

What are some steps or documentation to help normalize tables/views in a database? Currently, there are several tables and views in a database that do not use primary/foreign key concept and sort of repeats same information in multiple tables.

I'd like to clean this up and also somewhat setup a process that would keep relationship updated. Example, if a person zipcode changes or record is removed then it automatically updates its relationship with other tables row/s.

NOTE:* My question is to normalize existing database tables. The tables are live so how do I approach normalization? Do I create a brand new database with table structure I want and then move data to that database? Once data moved, I plug in stored procedures and imports?

NonProgrammer
  • 1,337
  • 2
  • 23
  • 53
  • 1
    Have you thought that your database could be de-normalized for a reason? Was it a data-warehouse and are you trying to normalize it because you mean to use it in a different way? The point is that normalization hels in reducing amount of data and inconsistency, but queries are more expensive... By the way, it's a question too broad and it's probably better if you look for a manual. For sure you need to understand what are your entities and relationships between them, maybe restarting from zero could be easier. – Mauro Piccotti Oct 12 '17 at 20:25
  • Hi. Literally dozens of info modeling & db design textbooks are free online in pdf & there are many academic slides (eg sfu.ca) & courses (eg stanford.edu). Re procedures/processes again there are intros & references for products in html & pdfs. Asking for such resources here is off-topic & asking for their content is too broad. Please read & act on [ask]. – philipxy Oct 12 '17 at 20:27
  • I think this is a valid question. This is from a perspective of an existing database setup. I am asking for step or a way to take existing database tables and normalizing them and then maintain that normalization when inserting more data to these tables. – NonProgrammer Oct 18 '17 at 21:32
  • Your post is jumbled. Also it (unclearly) asks 3 questions--how to do normalization, what code is needed to migrate & what code is needed to support the new schema. We told you, re normalizing find & follow a(n academic text)book then post details of where you are stuck. When you do that, give details of the old design you are starting at. When you have a design you can post a question with code & details re being stuck in migrating and another re being stuck in support coding. But just asking for designs or approaches is too general/vague/broad. – philipxy Oct 19 '17 at 20:29
  • Hi NonProgrammer . When there's more than one non-poster commenter on a post, we need to use `@` as in @MauroPiccotti for one of them to get notice of our comment. I just happened to wander by. – philipxy Oct 19 '17 at 20:40

1 Answers1

1

This question is somewhat broad, so I will only explain the concept.

Views are generally used for reporting/data presentation purposes and therefore I would not try to normalise them. Your case may be different.

You also need to be clear about primary / foreign key concept:

Lack of actual constraints (e.g. PRIMARY KEY, FOREIGN KEY) defined on the table does not mean that the tables do not have logical relationships on columns.

Data maintenance can be implemented in Triggers.

If you really have a situation where a lot of highly de-normalised data exists in tables for no apparent reason and you want to normalise it then this problem can be approached in two ways:

  1. Full re-write - I would recommend for small / new Apps
  2. "Gradual" re-factoring - large / mature applications, where underlying data relationships are complex and / or may not be fully understood.

Within "Gradual" re-factoring there are a few ways as well:

2.a. You take 1 old table and replace it with a new table and at the same time change all code that uses the old table to use the new table. For large systems this can be problematic as you simply may not be aware of all places that reference this table. On the other hand, it may be useful for situations where the table structure change is not significant and/or when the number of dependencies is small.

2.b. Another way is to create new table(s) (in the same database) in the shape / form you desire. The current tables should be replaced with Views that return identical data (to old tables) but sourced from "new" tables. This approach removes / minimises the need to modify all dependencies immediately. The drawback is that the View that replaces the old table can become rather complex, especially if View Instead Of Triggers are needed to be implemented.

Alex
  • 4,885
  • 3
  • 19
  • 39