Avatar billede kjarcarsten Nybegynder
05. februar 2013 - 12:33 Der er 8 kommentarer og
1 løsning

Struktur af databse - tabel, forespørgsel og formular

Jeg er ved at opbygge en kundedatabase - og af hensyn til at komprimere pladsen så meget som muligt, og gøre den så hurtig/effektiv som muligt ønsker jeg lidt hjælp

Har 1 tabel med kunde stamoplysninger - som er udgangspunkt for hovedformular.

Har omkring 20-30 tabeller, som skal med i denne hovedformular.
(nolge tabeller bliver opdateret 1 gang/måned)

Spørgsmålet er, om jeg skal lave 1 stor forespøgsel hvor jeg medtager fx 20 tabeller - eller om jeg skal lave en masse underformularer der indsættes til hovedformular?

Kan ikke overskue, hvad der er mest korrekt - dvs. giver den mest stabile opbygning samt svartider.

Der er op til 10.000 poster i kunde tabel, så total set vil der blive mange poster i databasen - hvorfor den forventes at blive lidt "tung".
Avatar billede terry Ekspert
05. februar 2013 - 18:30 #1
Find it hard to believe that you intend having 20 to 30 sub forms, sounds like you should re-think.

Having one query which contains data from all of the tables will very likley give problems with updating records, and very difficult to understand/change too.

A query can have maximum of 32 tables 255 columns
Avatar billede terry Ekspert
05. februar 2013 - 18:32 #2
If possible have forms which contain only 1 or two tables, depending on how they are related. Only open the form when it is necessary, not all at once in a main from containing many sub forms.
Avatar billede kjarcarsten Nybegynder
05. februar 2013 - 20:42 #3
Hi Terry,
Thanks for your answer.
I need all the information in 1 form!! (from all the tables)

Would it be better, if I make eg. 6 queries based on 20 tables?
And then make 6 sub forms in the main form?

If I have to reduce numbers of tables, I have to create fields in a table, where there will be lots of empty fields! Is this better than making lots of tables (to reduce "empty space") ??
Avatar billede terry Ekspert
05. februar 2013 - 21:02 #4
"If I have to reduce numbers of tables, I have to create fields in a table, where there will be lots of empty fields"

?
Dont understand the logic in that, why would that be necessary?

Without a better understanding of your requirements its not easy to give the right answer but I would try and use a few tables in a query as possible and as few sub forms as possible too.


You say "I need all the information in 1 form!! (from all the tables)"

why?
Avatar billede kjarcarsten Nybegynder
05. februar 2013 - 21:50 #5
sorry if I can't explain it..

first of all, my "main form" is a customer page, where all information shall be showed at once - there is a lot of information (app 50 different fields eg name, address, turnover, sales rep. ect.)

my "suggestion" of reduce number of tables is to minimize lost space eg:

to reduce tables, I could do this:

debitor tbl:
debitornumber
debitorname (eg 10.000 records)
sælger (eg.4.000 records = 6.000 records is empty) !!

to minimize "lost space / empty records) I can do it in this way:

debitor tbl:
debitornumber
debitorname (eg 10.000 records)

sælger tbl:
debitornumber
sælger (eg. 5.000 records)

which way is the best to optimize?
Avatar billede terry Ekspert
06. februar 2013 - 18:09 #6
which way is the best to optimize?

5000 to 10000 records is not a lot so I dont think it is a question of trying to optimize your database/tables to ensure good performance.

To be honest I think you need to look into database design, this may help you decide what tables fields you need.

http://www.youtube.com/watch?v=aBhK61KMqYs

Theres load of informationon the internet
Avatar billede kjarcarsten Nybegynder
12. februar 2013 - 20:20 #7
thanks for answer and link :-)
Avatar billede kjarcarsten Nybegynder
12. februar 2013 - 20:22 #8
point
Avatar billede terry Ekspert
12. februar 2013 - 20:44 #9
thanks. I hope the comments/link have been usefull in getting dB design right.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester