Workaround at continuous DB backend changes??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have about 600 objects in my DB, of which about 120 are tables.
Because of the DB design beeing altered all the time to be up-to-date

to the data reposition demands, DB structure is constantly beeing

altered, thus having to shutdown the db every once in a while to do

the changes.

After trying for many days to work this around, I found a solution for

which I would like your opinion:

I am thinking of concenarating most of the tables into one. This is

how:

Let's say I have two tables 1,2 with the following fields:
Table1: ID, DocDescription, Procedure, RespEmployee
Table2: ID, ProductDesc, ProdCategory, ProductCode


I thought of making a third table (table3) which has:
Table3: FileCategory, FileParameter, FileValue

Now,
FileCategory takes two values: Documents or Products
FileParameter takes values from another table which has as many

records as the fields in the above tables were (without the ID), such

as Procedure, RespEmployee, ProductDesc, ProdCategory, ProductCode.
FileValue is a field that holds for each of the FileParameter its

value.
In the form, the FileParameter combo takes its values according to the value
selected in the FileCategory combo.

Thus I am creating sort of a one-to-many (FileParameter to

FileCategory) relationship to withhold both tables.

That way, whenever a change is needed, whereas in the first case I

would have to shutdown the DB to make the changes, in the second case,

all I have to do is to add a new record to the table holding the

FileParameters.
How would you think of it as a thought?

Though here rises another issue. Many fields where related to other in

other tables and they were also many lookup fields (though I know they

are not recommended in tables). Any ideas on that???

Thank you in advance for your responses.
 
Jim

In looking at this post and your previous post (Allen responded to it), "red
flags" are going up for me.

It sounds as if you are working with data that is unknown-able and changes
all the time, and your "DB design [is] being altered all the time".

If you can't nail down a structure, I'm not sure how using a database will
be of much value.

Have you stepped back from what you now have and (?re-)considered the
normalization of your data?

How is it that the data you are working with changes in ways that cause you
to modify your database?

How have you currently modeled your data? (for example, have you created
tables to handle each "month" of data -- embedding data in the table names
works great ... for spreadsheets, but not for relational databases).

Good luck!

Jeff Boyce
<Access MVP>
 
Back
Top