vanekerenam
Registered User.
- Local time
- Today, 07:02
- Joined
- Dec 27, 2016
- Messages
- 11
so, could anyone check the database I made and see whether it makes sense in regards to the text provided?
It'd be much appreciated!
It'd be much appreciated!
Could use help designing small database :)
- Thread starter vanekerenam
- Start date
Frothingslosh
Premier Pale Stale Ale
- Local time
- Today, 10:02
- Joined
- Oct 17, 2012
- Messages
- 3,276
Ah, I thought Gizmo already had. I'll try to remember to do so after work, but I won't be home until after 5 (it's 7:26 now).
- Local time
- Today, 15:02
- Joined
- Jul 9, 2003
- Messages
- 17,444
No, I don't have access to Access at the moment.
Still, I think the questions (and many more) should be asked and answered before getting too deeply in to design.
Sent from my SM-G925F using Tapatalk
- Local time
- Today, 09:02
- Joined
- Feb 28, 2001
- Messages
- 30,209
vanekerenam,
Since you are still learning, here is my advice to you, some simple guidelines.
We have some workman's aphorisms here for building things. One of them for carpenters is "measure twice, cut once." The database equivalent is my Old Programmer's Rule #1 - "You can't do it in Access until you can do it on paper." Building a database "as you go" is (sadly) a commonplace activity, sometimes forced by circumstances. However, taking the time ahead of time to analyze the problem for elements that will need to be stored is incredibly important.
During this analysis, you run into the second Old Programmer's Rule: "Access won't tell you anything you didn't tell it first." That is, if you want X to be in a report, it has to be in the database for the report to be able to get it. Therefore, sometimes you have to work backwards from goal to data inputs in order to know what you need to capture in order to get the results you want.
Finally, one VERY simple principle will ALWAYS be correct (unless your teacher is trying to teach you some type of oddball workaround.) The closest English aphorism is "Don't let the tail wag the dog." That is to say, when you have a difference between your design and your data, your design is usually wrong because the data is (are) always right.
Since you are still learning, here is my advice to you, some simple guidelines.
We have some workman's aphorisms here for building things. One of them for carpenters is "measure twice, cut once." The database equivalent is my Old Programmer's Rule #1 - "You can't do it in Access until you can do it on paper." Building a database "as you go" is (sadly) a commonplace activity, sometimes forced by circumstances. However, taking the time ahead of time to analyze the problem for elements that will need to be stored is incredibly important.
During this analysis, you run into the second Old Programmer's Rule: "Access won't tell you anything you didn't tell it first." That is, if you want X to be in a report, it has to be in the database for the report to be able to get it. Therefore, sometimes you have to work backwards from goal to data inputs in order to know what you need to capture in order to get the results you want.
Finally, one VERY simple principle will ALWAYS be correct (unless your teacher is trying to teach you some type of oddball workaround.) The closest English aphorism is "Don't let the tail wag the dog." That is to say, when you have a difference between your design and your data, your design is usually wrong because the data is (are) always right.
vanekerenam
Registered User.
- Local time
- Today, 07:02
- Joined
- Dec 27, 2016
- Messages
- 11
Thanks a lot for your advice Doc Man, I'm sure it will come in as a great help when I need to build a dataset myself 
However, I was wondering if anyone could just take a quick look at the database I made, and see whether it makes sense in regard to the text I provided in my initial post. Whether the relationships make sense etc. You'll see the database is very basic, I didn't even put in any actual data, it's just about creating a more or less usable format.
I'll attach the database again quickly !
However, I was wondering if anyone could just take a quick look at the database I made, and see whether it makes sense in regard to the text I provided in my initial post. Whether the relationships make sense etc. You'll see the database is very basic, I didn't even put in any actual data, it's just about creating a more or less usable format.
I'll attach the database again quickly !
- Local time
- Today, 15:02
- Joined
- Jul 9, 2003
- Messages
- 17,444
I have reluctantly had a look at your sample database. The reason for my reluctance is I think you are a little bit early in making the design. There are questions to answer before you go on to design. However sometimes you can benefit from trying a design, it not working and then moving on. Database design is not just about getting the tables right and the links between the tables right. You can have one set of tables linked in one particular way for one task and then Linked in another way for another task. Relationships/linking is dependent on what you want to do, as much as the correct design of the database.
Really relationships are not set in stone and can change depending on what you want to do. This is one of my gripes with relationships, they can be misleading.
With regard to "Tbl_Station" the station name should just be a text field on its own and that's all you need. You don't need a lookup for it. In fact using a lookup in a table like you have is frowned upon, something to be avoided in good design. In other words have the text entries that appear in a combobox available from a lookup table. My approach is to have a separate table which just provides lookup information. I call the table "tlkpInfo" (Table Lookup Info). Now when you look up an item you save the unique ID for that item as opposed to the actual text. This saves you from some problems within your database, although it can cause some confusion when you start creating queries to look things up. Once you get the hang of it, you can solve these query problems quite easily. The island name should actually be a combobox lookup to the table you have already created "Tbl_Islands". Just link to the unique ID in the table "Tbl_Islands" do not extract the text. --- (I may have muddied the waters a bit here. For clarity, as I said at the Beginning you don't need a lookup for this value, it's just a text entry)
Regarding your table density "Tbl_Density" I don't understand it. I would need to understand what you want to do with the information before I could comment.
The Tables:- island, person, species, station, technique, are basically look up tables, there may be some minor alterations necessary but I think they're probably about right, although regarding the person table "Tbl_Person" it would be more common to record "deceased" than "alive". If these so-called lookup tables carry minimal information ,in other words just an ID and a text description, then consider putting them in one place, a lookup table which is used throughout your database for storing and presenting the lookup information.
I also think that you have Limited the ability to make contact with the persons in the table "Tbl_Person". You might benefit from a contact table constructed something similar to the one discussed in my Blog here:- Convert an Excel "Contacts" Spreadsheet to MS Access
In the sample table "Tbl_Sample" I'm not sure it's wise to add a sample name. I'm thinking that the sample name should be derived from the Colony and the Date. In other words it will automatically be created as you enter in the data this will also avoid accidental duplication of the sample name. I'm not sure whether it would be correct to record a sample ID and then the station ID or whether to record the station ID and then the sample ID. Again, I think this is something that needs to be questioned and discussed to get this the right way round.
I don't see provision for recording the actual colony anywhere?
As I said before there are loads of questions to ask before you get too deep into your Design. However what you've got so far looks good. Avoid creating lookups in tables. Avoid creating lookups of text values within a combobox. Store these lookups in a separate lookup table. Use the ID from the lookup table as an identifier, don't use the actual text.
Really relationships are not set in stone and can change depending on what you want to do. This is one of my gripes with relationships, they can be misleading.
With regard to "Tbl_Station" the station name should just be a text field on its own and that's all you need. You don't need a lookup for it. In fact using a lookup in a table like you have is frowned upon, something to be avoided in good design. In other words have the text entries that appear in a combobox available from a lookup table. My approach is to have a separate table which just provides lookup information. I call the table "tlkpInfo" (Table Lookup Info). Now when you look up an item you save the unique ID for that item as opposed to the actual text. This saves you from some problems within your database, although it can cause some confusion when you start creating queries to look things up. Once you get the hang of it, you can solve these query problems quite easily. The island name should actually be a combobox lookup to the table you have already created "Tbl_Islands". Just link to the unique ID in the table "Tbl_Islands" do not extract the text. --- (I may have muddied the waters a bit here. For clarity, as I said at the Beginning you don't need a lookup for this value, it's just a text entry)
Regarding your table density "Tbl_Density" I don't understand it. I would need to understand what you want to do with the information before I could comment.
The Tables:- island, person, species, station, technique, are basically look up tables, there may be some minor alterations necessary but I think they're probably about right, although regarding the person table "Tbl_Person" it would be more common to record "deceased" than "alive". If these so-called lookup tables carry minimal information ,in other words just an ID and a text description, then consider putting them in one place, a lookup table which is used throughout your database for storing and presenting the lookup information.
I also think that you have Limited the ability to make contact with the persons in the table "Tbl_Person". You might benefit from a contact table constructed something similar to the one discussed in my Blog here:- Convert an Excel "Contacts" Spreadsheet to MS Access
In the sample table "Tbl_Sample" I'm not sure it's wise to add a sample name. I'm thinking that the sample name should be derived from the Colony and the Date. In other words it will automatically be created as you enter in the data this will also avoid accidental duplication of the sample name. I'm not sure whether it would be correct to record a sample ID and then the station ID or whether to record the station ID and then the sample ID. Again, I think this is something that needs to be questioned and discussed to get this the right way round.
I don't see provision for recording the actual colony anywhere?
As I said before there are loads of questions to ask before you get too deep into your Design. However what you've got so far looks good. Avoid creating lookups in tables. Avoid creating lookups of text values within a combobox. Store these lookups in a separate lookup table. Use the ID from the lookup table as an identifier, don't use the actual text.
Last edited:
