seeking advice on setting up a database
I have previously managed to organize information about rock thin sections in a spreadsheet, but one winds up with lots of extra columns needed to keep track of everything, and lots of empty cells since not every mineral appears in every rock, and not all possible information about each mineral is recorded for every sample. Therefore I have been thinking of creating a database to keep track of it, but am running into issues with the design phase.
I have found pages on line that describe how to "normalize" your data to make certain you aren't wasting storage space on redundant data. One of them had an example where they started with one table that contains a list of student numbers, the name of each student's advisors, the room number of the advisor, and all of the classes each student takes. They then point out that it is better to break this into separate tables, and after a few steps they wound up with one table for showing which students have which advisor, one table showing which advisor has which room, and one which shows which students take what classes.
I can follow this logic, and if I had so few levels of information I would already be done creating my database. However, geology is more complicated than that.
I have a physical box containing (so far) 71 thin sections. Each thin section has a unique sample number.
Each thin section is composed of some number of minerals (generally 5 to 10 each, but there are some 20 to 50 minerals in total)
Each thin section has overall features that can be described, such as (but not limited to) foiliations, lineation, folds, brecciation, grain size distributions, presence or absence of phenocrysts (crystals noticeably larger than the rest of the minerals present)...
Each mineral has a number of describable features, some of which are quantifiable, including, but not limited to, grain size, grain shape, colour, texture, relief, birefringence... The same mineral can, and usually will, look very different with respect to some of those features from one thin section to the next, but other features will remain constant for at least some of the thin sections.
It is possible for there to be more than one type of a single mineral within a thin section--some which is original to the rock, and some which grew later due to metamorphic or hydrothermal changes to the rock. The later generation may have a very different composition from the original, and so might have different colour, crystal shape, crystal size, etc...
It is important to record the total composition of the rocks (thin sections) in terms of the % of each mineral that is present, and the sum of the amount of each mineral reported to be present in a given thin section must add up to 100%. this can help determine the rock type.
There are many possible names of rock types, and each thin section should, eventually, be assigned to a rock type. However, often there is more than one name that could apply to a given sample.
Sometimes, but not always, the overall features of the thin section (like foiliation) is defined by one or more minerals, and the other minerals present are not part of that feature
the thin sections come from a variety of drill holes, each with a unique identification number
the drill holes are located in a variety of different regions within the area, each with a unique name.
The area is the first one with which I will be working, but if my contract is extended after two years on this job I will likely expand the project into other areas, each with a unique name
Furthermore, sometimes there will be minerals that are unidentified in the short term. It would be helpful if it is possible to call them things like "unknown mineral with really high relief" or "unknown green mineral" or something so that if I see the same thing in another sample I will know that they are the same, so that when I figure out what mineral it is I can give them all a name at once. However, it would probably be helpful if, in these cases, rather than just replacing the name it let me look at the each other ones I thought might be the same thing to determine if they really are, now that I know what it is in the first sample--this is because there are some minerals which are similar in one feature (say relief) that are totally different in another (say birefringence), and if I happen to have looked at one thin section with a high relief unknown on one day and on a different day saw another with a high relief unknown and sometime later I determine what the mineral was in one sample it might turn out that they are not all the same.
At some point I will likely analyze some of these samples on an electron microprobe, in which case I will have composition data for, hopefully, all of the minerals within a given thin section. There will usually be multiple analysis points on a single crystal, and multiple crystals of each mineral type in the sample analysed. For each analysis point there will be numbers given for a variety of different oxides (e.g. FeO, Al2O3, SiO2, etc.)
In addition, for every sample I currently have thin sections for there will soon be "whole rock" composition data (crush the sample and analyze what the whole thing is made of). It will be important to compare what minerals are associated with what composition types.
I suspect that to those of you who are used to working with databases this will seem like a very simple, straightforward, easy to solve problem. If so, please tell me how to approach this! How many tables to I need to create? How do I best link them?
I have found pages on line that describe how to "normalize" your data to make certain you aren't wasting storage space on redundant data. One of them had an example where they started with one table that contains a list of student numbers, the name of each student's advisors, the room number of the advisor, and all of the classes each student takes. They then point out that it is better to break this into separate tables, and after a few steps they wound up with one table for showing which students have which advisor, one table showing which advisor has which room, and one which shows which students take what classes.
I can follow this logic, and if I had so few levels of information I would already be done creating my database. However, geology is more complicated than that.
I have a physical box containing (so far) 71 thin sections. Each thin section has a unique sample number.
Each thin section is composed of some number of minerals (generally 5 to 10 each, but there are some 20 to 50 minerals in total)
Each thin section has overall features that can be described, such as (but not limited to) foiliations, lineation, folds, brecciation, grain size distributions, presence or absence of phenocrysts (crystals noticeably larger than the rest of the minerals present)...
Each mineral has a number of describable features, some of which are quantifiable, including, but not limited to, grain size, grain shape, colour, texture, relief, birefringence... The same mineral can, and usually will, look very different with respect to some of those features from one thin section to the next, but other features will remain constant for at least some of the thin sections.
It is possible for there to be more than one type of a single mineral within a thin section--some which is original to the rock, and some which grew later due to metamorphic or hydrothermal changes to the rock. The later generation may have a very different composition from the original, and so might have different colour, crystal shape, crystal size, etc...
It is important to record the total composition of the rocks (thin sections) in terms of the % of each mineral that is present, and the sum of the amount of each mineral reported to be present in a given thin section must add up to 100%. this can help determine the rock type.
There are many possible names of rock types, and each thin section should, eventually, be assigned to a rock type. However, often there is more than one name that could apply to a given sample.
Sometimes, but not always, the overall features of the thin section (like foiliation) is defined by one or more minerals, and the other minerals present are not part of that feature
the thin sections come from a variety of drill holes, each with a unique identification number
the drill holes are located in a variety of different regions within the area, each with a unique name.
The area is the first one with which I will be working, but if my contract is extended after two years on this job I will likely expand the project into other areas, each with a unique name
Furthermore, sometimes there will be minerals that are unidentified in the short term. It would be helpful if it is possible to call them things like "unknown mineral with really high relief" or "unknown green mineral" or something so that if I see the same thing in another sample I will know that they are the same, so that when I figure out what mineral it is I can give them all a name at once. However, it would probably be helpful if, in these cases, rather than just replacing the name it let me look at the each other ones I thought might be the same thing to determine if they really are, now that I know what it is in the first sample--this is because there are some minerals which are similar in one feature (say relief) that are totally different in another (say birefringence), and if I happen to have looked at one thin section with a high relief unknown on one day and on a different day saw another with a high relief unknown and sometime later I determine what the mineral was in one sample it might turn out that they are not all the same.
At some point I will likely analyze some of these samples on an electron microprobe, in which case I will have composition data for, hopefully, all of the minerals within a given thin section. There will usually be multiple analysis points on a single crystal, and multiple crystals of each mineral type in the sample analysed. For each analysis point there will be numbers given for a variety of different oxides (e.g. FeO, Al2O3, SiO2, etc.)
In addition, for every sample I currently have thin sections for there will soon be "whole rock" composition data (crush the sample and analyze what the whole thing is made of). It will be important to compare what minerals are associated with what composition types.
I suspect that to those of you who are used to working with databases this will seem like a very simple, straightforward, easy to solve problem. If so, please tell me how to approach this! How many tables to I need to create? How do I best link them?