I just realized my database is not 1NF compliant. Is it necessary that I rebuild the tables?

I just got a job, and they asked me to build all of the queries for their database. So I did, hundreds of them. Now, the database is being populated by a separate department, but in reviewing my 开发者_如何学运维colleagues work on the tables, I realize my db is not 1NF compliant. For instance, one of our tables has the following fields:

Address 1 Address 2 Address 3 etc all the way to Address 6

I'm wondering if I need to redo it or not. My boss knows nothing about databases (and neither do my co-workers), and even though I think it will be more efficient if I rebuild it, I am wondering if it's necessary given our time constraints (we are 2 months behind our deadline).

What's your opinion? What are your thoughts? How are you doing today?

---

Before you get ahead of yourself and rewrite the entire thing, ask your colleague why they chose that design. Sometimes design choices seem strange, but are done that way for a reason. Maybe thats how the addresses arrive from the source, or thats how they are transferred in another system. Don't jump the gun just start changing everything if you're not sure on why they have it designed that way just because its not 1NF.

---

There's the "right" answer, and the right answer.

The "right" answer is that 6 separate address fields will be a nightmare to maintain, and normalizing that out to an address table would reduce that burden considerably.

The right answer is that you're already 2 months behind deadline. If you can fix it behind-the-scenes so that people are still using the same queries and stored procedures (that is: calling them the same), then I would wait until you've pushed to production and then go back in to clean that up as an 'enhancement' later.

The main qualifier to this, of course, is performance. If you're suffering performance issues (I sincerely doubt it, just based on this) then you'd want to fix those, deadline or not.

---

1NF is commonly misunderstood.

If you read about repeating groups in the Wikipedia article on 1NF, I can see how you may have come away with the idea that your table with attributes Address_1, Address_2, ..., Address_6 violates 1NF.

I suggest you instead read Facts and Fallacies about First Normal Form, specifically the section on 'The ambiguity of Repeating Groups'.

From your description it would appear your table is in 1NF (and possibly a higher normal form too) because is is relational, as far as that terms can be applied to Access i.e. tables of rows (no duplicates) and columns (no duplicate names), the intersection of which is a scalar value, and (being strict) no nulls.

But, although having established your table is in 1NF (barring other information you haven't revealed), it is likely it will suffers from other design issues. In short, users may find it a pain to write queries for because in SQL the unit of work is the row i.e. easier to query if the six address values are in rows rather than columns.

Before you change anything, I agree with @Fink that it would be useful to know the original designer's intention. One possible reason for the chosen design is to make it easy on the SQL DDL coder to write constraints. For example, the business rule was that each entity must have exactly six address values is almost impossible to implement any other way than to have six NOT NULL columns on the same row (e.g. a table-level CHECK constraint is tempting but there is a problem in Access in that it checks constraints at the row-level, rather than at the statement level, and has no mechanism for deferring a constraint).