Hello,
I am trying to build a database for animals in many lakes. The goal for the database is to be a base for creating overviewmaps where you can see which species exist or have existed in different lakes with specified properties.
I imagine the structure of the database in this way:
Table 1: Lakes with names and basic data about lakes in different fields. LakeID is primary key.
Table 2: Physical and chemical properties in different fields. FysChemID is primary key and LakeID is foreign key.
Table 3: Different species names in one field. SpeciesID is primary key.
Table 4: Different methods for catching the animals in one field. Methods ID is primary key.
Table 5: Year for catching the animals in one field. YearID is primary key.
Table 6: Name of the person who catched the animals in one field. PersonID is primary key.
Table 7: Different fields about the existence of animals, for example "have you ever noticed the animal in the lake", "Which year was the last you noticed the animal", "never noticed".
Table 8: A many to many table where all the primary keys above is foreign keys. Primary key is ManyID. (I have even tried to use composite primary key instead of foreign keys).
I have tried to make a composite form for reading data and to write data into the tables. I have tried with and without subforms, but I have got problems.
My questions are:
How to design the database with good relations and keys?
How to build good forms for reading and writing?
I don´t have long experiences of making databases, but I am very interested in learning more.
Bjorn
I am trying to build a database for animals in many lakes. The goal for the database is to be a base for creating overviewmaps where you can see which species exist or have existed in different lakes with specified properties.
I imagine the structure of the database in this way:
Table 1: Lakes with names and basic data about lakes in different fields. LakeID is primary key.
Table 2: Physical and chemical properties in different fields. FysChemID is primary key and LakeID is foreign key.
Table 3: Different species names in one field. SpeciesID is primary key.
Table 4: Different methods for catching the animals in one field. Methods ID is primary key.
Table 5: Year for catching the animals in one field. YearID is primary key.
Table 6: Name of the person who catched the animals in one field. PersonID is primary key.
Table 7: Different fields about the existence of animals, for example "have you ever noticed the animal in the lake", "Which year was the last you noticed the animal", "never noticed".
Table 8: A many to many table where all the primary keys above is foreign keys. Primary key is ManyID. (I have even tried to use composite primary key instead of foreign keys).
I have tried to make a composite form for reading data and to write data into the tables. I have tried with and without subforms, but I have got problems.
My questions are:
How to design the database with good relations and keys?
How to build good forms for reading and writing?
I don´t have long experiences of making databases, but I am very interested in learning more.
Bjorn