From Flat to Normalized Many-to-Many

[Andren]

Dear all!

I first wish you all a happy and prosperiuos New Year.

Not just in coding.

Over the last year I tried to build a research database for specialized litterature. Despite my dumb questions I got great help from many of you.

However - I failed to make a working normalized many-to many database structure.

Then someone told me to make a flat-database and populate it with som data and thereafter try to breake it down in adequate subtables by the help of Access itself.

However - now I have written a lot of code to do this and that.
And the database is now working....

What will happen to all code if I try to "normalize" it afterwards ?

Or - what should I do? Start over again ?

hanks for all suggestions.

/ Anders

 

[dcx693]

The question of what to do at this point depends on many factors relating to the database:
1- is it doing what you need it to do now
2- will it still work in the near future? Realize that needs change often.
3- will it work long-term? As databases grow larger, flaws in the initial design tend to become magnified.

Those factors are affected just as much by cost and time as well, so factors those in.

 

[Andren]

dcx693

Thanks for answering.

1. Yes it does what itshould
2. No , I can't antecipate any changes needed in the next coming decades (and by then I will be dead - so what the he.... )

OK - Sorry for beeing selfish ...but I am tired of it by now.

/ Anders

 

[Pat Hartman]

Here is a working many-to-many sample db.
Many-To-Many

Changing your structure at this time will entail changes. At a minimum you will be moving from a single form to a mainform with a subform. Your reports will probably be fine since you can use a query to rebuild the flat structure from the normalized structure.

 

[Andren]

Many-to-Many (problems...)

Thanks Pat !

After retirenement I'll learn how to play Bridge

/ Anders

 

[Andren]

Still....

I am still having a question in mind...

This "database" will in a few years contain thousands of reccords about litterature (books,papers,dissertaitions, reviews, drafts etc etc) about research done by the help of a semi-structured interview called Addiction Severity Index (ASI) in various fields (psychiatry, psychologi, sociology etc) mentioning various problems (specific populations, specifik diagnostic groups, specifik .... you name it...). I have covered all that (I think...) in my flat-data-base design - from the point of view of my "expertise" as a clinical and research psychologist. So I am not so worried any more about the content of my database.

So - what would happen if I kept the flat-data-base design ?

Would it explode in some way or another - or would it be to slow to work with. I have populated it with about 100 reccords and that works fine.

What will happen when I have 1000 reccord or 10 000 reccords ?

When Pat suggests that I have to work with Forms - Subforms again, I feel heavy in my heart, because I tried it and it gave me a lot of problems.

As it is now I have one main continious form for input of data that looks good and that works. OK, OK - I am lazy. I realize this as I write. Pat is right.

Still I am wondering what will happen when I have 100 000 reccords in my flat structure.

/ Anders

 

[Pat Hartman]

There is no time like the present. Once you learn, you'll kick yourself for not doing it earlier. Go to the American Contract Bridge Leage site ACBL and download the teach yourself to play bridge program. You should also be able to find a club in your area and they can introduce you to local teachers.

Once you reach 100,000 records your database will be larger than it needs to be because of the duplicate data and at a minimum queries will be slower than they need to be.

Some flat structures are worse than others. Since we don't know what yours looks like, it is hard to say where you might run into problems.

 

[Andren]

My flat file is no longe flat - it's deep

Thanks Pat !

I worked all day and tried to normalise my flat structure and it worked quit well. I was afraid for all sub-forms but they did not mess with me either - like they did before.

I think this Forum is extraordinary. I am almost 60. One year ago I had no idea about programming whatsoever. From interaction with all of you - from USA, England, Australia - from reading all the posts - I have learned and set up something that might be good for a lot of professionals in the area.

So I learned a lot from this Forum - and I am so grateful.

I wish that the friendly,helpful and creative sprit of this Forum could be transmitted to other areas like politics, global problems that have to be adressed etc.

About 30 - 35 years ago - it was very cold winter - my wife and me attended a party. In this party there was a Duch professor of technology. We discussed politics, Vietnam War and other disasters. The he said "In about the shift of Millenium, we will have computers, that are small and can communicate. Then we will spread them over the world. When all oppressed people can take part of all the knowledge they will get rid of their opressors and dictators and they will communicate with each other for the benefit of all". The we walked home in the night and said "..what a weired professor...". But he was quite close. Don't you think?

All the best.
/ Anders