Is adding a new table/column to mature database bad? (1 Viewer)

Assume we have a properly normalized database. It has served its purpose well. Come along a manager who asks that we now track some extra information that previously wasn't needed/required/applicable.

To encapsulate the new requirement, adding tables and/or fields may be necessary.

Would it be wrong to say that as long you *merely* add, but do not modify or delete any other data structure, the process is quite trivial? Or will it require re-considering the whole schema even to add a new column?

I guess the answer for this is, "It depends." It all depends on what the data is and how you may have to add it. There is no cut and dried answer really.

I would say its a trivial process as long as the new field goes not violate any of the normal forms.

Define "mature" database. (Rhetorical question.)

A mature database is one that has outlived its function.

Of COURSE you will need to add things. The world changes around you on a near-daily basis.

I second the opinion of everyone else who has answered.

If you add fields that don't violate normalization rules, you have done nothing to substantially alter the relationships.

Some things you could do that are not as easy:

1. Add fields to supercede existing fields - potentially duplicating data. If any other Access element references a superceded field as PK/FK relationship, you are looking at possible restructuring, which can have deleterious effects.

2. Add new fields to implement new relationships that potentially involve new referential integrity constraints. This can affect your ability to update records as you used to before the change - i.e. your users might not be able to use the same procedures they used before.

3. Add new tables that lead to #2 becoming an issue even for old tables that were not changed.

4. Add new fields that must be indexed. (Affects performance and there is a limit on the number of indexes on a table.)

Great, that's good to know.

The reason I asked this was because I'm debating about whether a generic one-size-fits-all lookup table for a table of attributes (which I cannot know in ahead of time) for validation purposes would be worse in normalization terms than creating new lookup tables as I add new attributes to the table of attributes.

Of course, if any of attributes may use a similar lookup, I will re-reference the same lookup table, but I reasoned that it was better to add new lookup table, if I need it, for new attributes than trying to bludgeon it into generic lookup table as there's no guarantee that the generic lookup will fit the needs of new lookup table.

I have a set of standard forms/reports and a table that I use for ALL simple lookup type tables. I do this because I don't want to be in the list maintenance business. I want my users to maintain their own lists but I don't want to keep building new maintainence forms. Of course, they can't add new lists since that would require other changes such as adding a new column or table and adding fields to forms/reports. But, they can add new rows to existing tables. The structure is very simple.

UniqueID (autonumber)
TableID (foreign key to UniqueID) used to group items into a list.
LongDesc
ShortDesc
ActiveFlag (Defaults to yes. Can be set to No to prevent future use but allow item to remain for historical reference)
ChangedBy (autofilled by BeforeUpdate event of maintenance form)
ChangedDate (autofilled by BeforeUpdate event of maintenance form)

I even enforce RI which prevents the users from deleting entries that have references from other tables. Due to the limitations of Access, only around 35 relationships can be made to a single table so if you end up with more than that, you need to decide what to do about RI.

In order to make populating combos easy, I create a query for each table and name it:
qLkupSomeField_111
With SomeField being the field name and the 111 being the value of the tableID. So all the queries are identical inside except for the selection criteria. I could have used a single query with an argument but I use these so often, I perfer to have them spelled out.

Pat, interesting idea!

Can you please clarify this:

Pat Hartman said:

UniqueID (autonumber)
TableID (foreign key to UniqueID) used to group items into a list.

Click to expand...

I think that's supposed to be a key from another table to group the tableID... is that what you mean?

(Also, great to see you again! )

Banana, I always enjoy your interesting and thought provoking posts. Sometimes though I feel you take the rules a little too literally. I prefer to take a pragmatic view of "Best practice" and do what is best suited in a particular context. I feel that often a database does not need to be normalised beyond 3N.

At the end of the day my design criteria is to produce something that does the job and is easy to maintain. I find a knowledge of design best practice is advantageous if only because I then know when and why I am taking a short cut.

I welcome any comments on this - After many years in IT I have a thick hide.

Rabbie,

You may be right. The only reason I ask so many questions is because while I'm mainly self-taught, I also have this little voice in my head telling me that I'm missing something very very basic or fundamental.

In my case, I do not always know whether I should be taking the short cut or not or maybe I only have a collection of vague notions as to why I should/n't be taking it, so I'd prefer to ask now than get shinned later.

I did not mean to sound critical. I guess it is the pragmatist v Purist clash. I commend your desire to find out the correct way of doing things. It is always dangerous to bend the rules unless you know why you are doing it. In the final analysis I am sure your DBs will be models of how it should be done. It is a good job we are all different or there would be a very dull and uniform world.

My code table is a self referencing table similar in concept to an employee table where each record has a supervisorID which refers to a different record in the employee table since the supervisor is also an employee. So one of the "tables" in my code table is a "table of table names".

If it is easier to understand, you can use two tables. The parent table will hold a list of tables and the child table will contain the lists that belong to each table in the parent table.