Design Advice

[gripper]

I am looking for some solution design ideals.

I have an Access DB that tracks domains that I manage. One of the data points I want to track is the keywords that are used. Currently I keep the keywords in an excel spread sheet.

Example lets say I have a domain in my DB for abcdomain.com I then have a link to a local excel sheet that is label abcdomain_com.xlsx.

What I want to try to do is to have a table named "Keywords" and set up a relationship to my domain table.

Now when I gather keywords (100's) I need to import that keyword list and all the columns of data into the keyword table and associate it directly to the domain.

I need to be able to recall these keywords at a later date for other purposes through a query.

I am getting tired of having everything spread out with excel sheets.

I love to get ideals on how to structure this and implement a simple import wizard. The trick is I need to make sure that the list of keywords are attributed to the right domain when imported.

Any ideals would be great.

Thanks members

 

[jackd]

Please provide a sample of what you are planning to store re Keywords and domain.

 

[Hermanito]

I am assuming that one keyword can be used in multiple domains?
In that case, you would need a database structure of three tables:

table1 = Domains
table2 = Keywords
table3 = Domains_Keywords

The link between Domains and Keywords in this case would be a so-called N-to-N relation, which is implemented by using an intermediate table (table3). N-to-N means that each record of one table can be linked to an arbitrary number of records of the other table, and this in both directions. So, one domain can have multiple keywords, but also: one keyword can apply to multiple domains.

Table3 is a simple table with only 2 fields: the primary key of table1 and the primary key of table2.

When you import a list of new keywords, this can get a little complex: not only do you have to create records in table2 for each keyword that is not yet in there, but you also have to create records in table3 for each link between the domain for which you are importing that list, and the keywords themselves.

I hope that gives a bit of an idea what might be waiting for you
Feel free to ask questions of course...