Recommendations for good database design of MS Access Database
We have an Access database which contains a list of offerings.
Every single offering needs to have these information supplied:
ID number of the offering
Country and City
Name of the offering
Since we collect data for various types of offerings from a couple of affiliate companies, these offerings may have some additional properties, such as descriptions, star rating of the hotel, GPS coordinates etc.
These additional properties may or may not repeat for every single affiliate company.
In the moment, we currently have about 30+ properties collected from different affiliate companies, some of those are in common, some are not.
Currently, what we do is that we create a list of all of the possible available offering additional properties - We assign it an ID number and we give a name to that property, so it's clear enough what that property is about (Table 1).
After that, we link an offering and the additional property in many-to-many table, where we have all of the informations about the property (Table 2):
What's the ID number of the property?
What's the ID number of the offering?
What's the value of the property?
When was it imported?
Who updated it and when?
Was the update manual or automatic?
We currently put all of the values into one "long text" field, regardless if it's the single line field, multi line field, a number or a date. We do it this way, because it's easier for maintenance (if we receive some new property, we add it in the properties list table, and we mark it for import in our import template, so it gets automatically imported from the Excel sheet provided by the affiliate company). This approach to the data is simpler for us, because the properties may vary from one affiliate company to another, and there might be a lot of new properties in the future. By doing it this way, it's a lot easier for us to modify our export procedure, since the script made for export procedure will check only one field.
However, we are limited with this type of database design in a way where we can't display properties in a continuous form with custom height of these properties (for example, we need multi line textbox for large fields like descriptive text, and we need a simple single line field for a property like star rating). We did a way around, by marking some specific properties as multiline in Table 1 (mentioned above), and then those are being displayed in one subform, and the others are displayed in another subform. Otherwise, we are limited in a way where all of the properties have the same height (we don't need multiline field for properties which are obviously not multiline and vice versa, we can't have single line textbox for fields which are obviously multiline fields).
Another problem is that we are mixing a variety of data types in the same field. We basically have text, numbers, dates etc. placed inside this "long text" data type field. Again, this is easier for us because we also have custom export to text procedure, and all of these properties should be considered as text in the end (we would still need to convert numbers to text, once we export the data).
Is this database design the right one, or do you suggest some other type of database design and why do you recommend it to be that way?
Should we simply create separate tables for each affiliate company with their specific additional properties of the offerings? Should we split different data types in a separate columns?
We need to know this because we are going to undertake a large Access database project and we want to make sure the database design is the one that will be most stable, least prone to bugs and issues and is easy to maintain.
Thank you
John
Every single offering needs to have these information supplied:
ID number of the offering
Country and City
Name of the offering
Since we collect data for various types of offerings from a couple of affiliate companies, these offerings may have some additional properties, such as descriptions, star rating of the hotel, GPS coordinates etc.
These additional properties may or may not repeat for every single affiliate company.
In the moment, we currently have about 30+ properties collected from different affiliate companies, some of those are in common, some are not.
Currently, what we do is that we create a list of all of the possible available offering additional properties - We assign it an ID number and we give a name to that property, so it's clear enough what that property is about (Table 1).
After that, we link an offering and the additional property in many-to-many table, where we have all of the informations about the property (Table 2):
What's the ID number of the property?
What's the ID number of the offering?
What's the value of the property?
When was it imported?
Who updated it and when?
Was the update manual or automatic?
We currently put all of the values into one "long text" field, regardless if it's the single line field, multi line field, a number or a date. We do it this way, because it's easier for maintenance (if we receive some new property, we add it in the properties list table, and we mark it for import in our import template, so it gets automatically imported from the Excel sheet provided by the affiliate company). This approach to the data is simpler for us, because the properties may vary from one affiliate company to another, and there might be a lot of new properties in the future. By doing it this way, it's a lot easier for us to modify our export procedure, since the script made for export procedure will check only one field.
However, we are limited with this type of database design in a way where we can't display properties in a continuous form with custom height of these properties (for example, we need multi line textbox for large fields like descriptive text, and we need a simple single line field for a property like star rating). We did a way around, by marking some specific properties as multiline in Table 1 (mentioned above), and then those are being displayed in one subform, and the others are displayed in another subform. Otherwise, we are limited in a way where all of the properties have the same height (we don't need multiline field for properties which are obviously not multiline and vice versa, we can't have single line textbox for fields which are obviously multiline fields).
Another problem is that we are mixing a variety of data types in the same field. We basically have text, numbers, dates etc. placed inside this "long text" data type field. Again, this is easier for us because we also have custom export to text procedure, and all of these properties should be considered as text in the end (we would still need to convert numbers to text, once we export the data).
Is this database design the right one, or do you suggest some other type of database design and why do you recommend it to be that way?
Should we simply create separate tables for each affiliate company with their specific additional properties of the offerings? Should we split different data types in a separate columns?
We need to know this because we are going to undertake a large Access database project and we want to make sure the database design is the one that will be most stable, least prone to bugs and issues and is easy to maintain.
Thank you
John
ASKER
Attached is a screenshot showing the relationship on which we need clarification on:
"PlaceholdersImport" is the table that contains a list of properties names, which we later on use as placeholders on export procedure
"Companies" is the table with the list of the affiliate companies
"Links" is the list of offerings
"LinksProperties" is the table that contains the list of the particular offering additional properties
"PlaceholdersImport" is the table that contains a list of properties names, which we later on use as placeholders on export procedure
"Companies" is the table with the list of the affiliate companies
"Links" is the list of offerings
"LinksProperties" is the table that contains the list of the particular offering additional properties
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your assistance. It is much clearer now what we need to do.
thanks