Redesign, expand your database without breaking it

[SunTop]

Hello everyone



When trying to redesign, expand or add more functionality to an existing database with lots of data in it, what type of tasks can break your database ?
For example if you do any of the following things:
- adding a column to a table
- adding a new table
- renaming a column in table
- changing the data type of a column
- adding a relation to a table
- renaming a text box, a combo box or a button on a form or a table
- changing the validation rules of a column

Some tasks like deleting a table or a relation will obviously break your database, but what about other tasks I mention above ? how can I make sure that when I change a design aspect in my database that I will not break it ?

[davegri]

I would say that all of the above could cause problems except the simple addition of a table.
Changes always require testing and debugging, no way to avoid it.
Make changes to and test a copy of the database. When satisfied that the changes have the desired result, put the copy into production.

[SunTop]

How about adding a report or a form or a query ? can this break my database ? do I need to test my database after adding one of these objects ?

[John_G]

Some things to watch out for for each (this by no means a complete list!):

- adding a column to a table

May only be an issue if you have VBA code with some forms of INSERT INTO

- adding a new table

Should not be a issue

- renaming a column in table

Will potentially cause problems with forms, reports or queries that refer to the column. VBA in forms may not compile or cause runtime errors

- changing the data type of a column

Depends on what the change is. Reducing the field size can cause data loss (primarily affects text fields). Changing numeric type (e.g. integer to long) is usually relatively safe, but can cause data loss (e.g. single to integer). I recommend you not mess with date/time data. Changing from text to numeric or the reverse can lead to syntax errors or type-mismatch errors all over the place in queries and VBA.

- adding a relation to a table
Not usually a problem; Access will tell you if you can't do it or if there is a data issue preventing you from setting some properties of a relation.

- renaming a text box, a combo box or a button on a form or a table

Usually only causes problems with the form's VBA code, or in queries that reference the renamed control.

- changing the validation rules of a column

I cannot be sure of this one, but the validation rules are usually applied only to new data; existing data that may no longer be "valid" is left as it is, until you try to change it.

How about adding a report or a form or a query ? can this break my database ? do I need to test my database after adding one of these objects ?

Just adding these should not be an issue, because nothing references them yet anyway. A badly written action query (Update, Delete, Insert) could make a real mess of the data in a hurry, so ALWAYS have a backup first. The same is true if you have potentially damaging VBA code in a form or module.

I would suggest that you make changes incrementally, i.e. one thing at a time, making sure one change works correctly (testing all possible scenarios if you can) before moving on to the next one. Finding the source of an error after you have made a lot of untested changes can prove to be "difficult".

[Homegrownandy]

If your database is split, create a copy of the back end if you want to test forms or querys. Then any additional incorrect data you create wont matter when you replace the backend with the copy. Make sure to test everything.

[orange]

Get a statement of requirements from whomever is paying for the proposed changes.
Clarify any requirement until you understand it as well as the sponsor.
Build a simple prototype and confirm what it does with the project sponsor (just as simple as possible- no detail/just concepts)
Based on an approved prototype, design the change -including the steps to get there - based on more thorough analysis
Always test on a copy!!!!

This is from DatabaseAnswers.org

These are the Steps in a Top-Down Approach :-

2. Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
3. Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
4. Analyze the Things of Interest and identify the corresponding Tables.
5. Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
   For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities. If you are just starting out, I suggest that you postpone this level of analysis.
6. At this point, you can produce a List of Things of Interest.
7. Establish the relationships between the Tables.
   For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
8. Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
9. Identify the Static and Reference Data, such as Country Codes or Customer Types.
10. Obtain a small set of Sample Data,
    e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
    "He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
11. Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
    For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
12. Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
13. You need to define a Primary Key for all Tables.
    For Reference Tables, use the'Code' as the Key, often with only one other field, which is the Description field.
    I recommend that names of Reference Data Tables all start with 'REF_'.
    For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
    This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
    However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
    It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be. Which means, of course, that it often never gets done.
14. Confirm the first draft of the Database design against the Sample Data.
15. Review the Business Rules with Users,(if you can find any Users).
16. Obtain from the Users some representative enquiries for the Database,
    e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
17. Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
    Development staff, etc. and repeat until the final Database design is reached.
18. Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.

---

Good luck.