27. februar 2004 - 17:11Der er
11 kommentarer og 1 løsning
Relationer og måden at gemme data på, hastighed.
Jeg vil til at granulere mine databaser noget mere og bruge relationer, har læst en del på det men har lige et par spørgsmål.
1) Det at man trækker linier imellem feldterne i sin db i access's relations vindue, har det nogen betydning udover at man selv senere kan sidde og se hvad man egentligt hat tænkt sig da man lavede databasen? Man skal jo definere sine forespørgelser i SQL'en alligevel?
2) Hvis nu man har en db som indeholder 2 tables
Table-1 Navn Adresse etc.etc. HusDyr
Table-2 HusDyrID HusDyr
En person har 15 husdyr. Lægger man HusDyrID fra Table-2 ind i HusDyr i table-1 sepereret med komma : 12,35,456,278,etc. etc. eller hvad gør man smartest for at spare plads og øge hastigheden i databasen. Der skal hovedsagelig vises data og ikke så ofte indsættes data. Det skal bruges online på .asp
A person(house or what ever) can have one or more pets (cat, dog, rabbbit etc.)
and a pet can be owned by on eor more persons. So you have what is called a many-to-many relationship. And as is always the case in this type of relationship you need THREE tables. The third table contains links between the other two. An example:
Thanks Terry, then this would be the correct and fastes way to do it.
Do you have a comment on question 1?
1) Does it have any effect on the database that one draw the lines between tables and fields in the Relationship window, defining the relationship One to Many - Many to Many and so on? Or is it just to help the developer with a visual guide to what he is doing and thinking when building the database. I mean, you have to define your queryes any way, its not like you just SQL your main table and all relations to this automatically follows with it. Select * from Table-1 Where HusNrId = '1' Wouldent give me any data from other tables bound to Table-1 HusNrId 1 anyway
By using the relationships window you can also alter the realationship type and also the referential integrity. Right click on the thin line and you will see what I mean. But here is NO NEED to use this! If you use it and make a query then the relationships will get added automatically!
Do you have a link to some site really digging deep into the borders of Normalisation, De-Normalisation where to set the limit to keep the speed up on your databases. The queryes can get pretty complicated and heavy on the database if i Normalise to much.
What will help is to make sure that fields you use often in your WHERE criteria are indexed. I dont have any links concerning limits in Normalisation I have one which may be usefull. and You will be able to find many on th eweb too.
I have searched the net for info on it, already read all of the chapters on hedeskov.dk, but i cant seem to find any conclusion, explanation or guide saying that "exceeding 5 or more tables in a search criterea will make your db slow". Well i can see that such a guide is very difficult to make as it dependts on many factors on each and every db. Thanks again Terry, your info was a great help :-)
where have you seen that joining five tables will slow the dB? It depends on many factor. As mentioned previosuly make sure your WHERE criteia fields which you often use are indexed, and only select the fields you need NOT SELECT * (ALL)!
I have read it a lot of different places, and it seems logical to me. For each table that you include in a query, the db wil have to run through that table, so running through 1 table instead of 5, would sound more fast. Offcourse have data from 5 tables stored in one would make the table quite big, and hold a lot of data more than once (Redundance I think its called), and make search time big......... I also read some where (A loong time ago) that selecting * instead of defining the fields would speed the queryes up, something about access not needing to sort the data or something like that. Havent really used the * anyway, cause it seems unlogically that it would be faster. I would presume that using the * would just fill your RAM and use more system resurces.
Synes godt om
Ny brugerNybegynder
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.
