Im so green it hurts. Newbie question.

[travisdu]

Hi all,

Im looking to create a data base but it does not
fit all the normal database structures that I have
seen on the web so I thought I might ask here and get
some feedback.

Most databases seem to be one to many and I think I need
a many to many.

Basically im building a keyword repository where the primary column would be two word keywords like "yahoo email". Each two word keyword has a set of derivations of
that keyword such as "yahoo email box","yahoo email password", "yahoo email hack" etc. and a final column with
related catagory keywords such as "hotmail email", "aol email" etc etc.

Most of the databases ive seen have a primary column and individual columns after that that contain a single item of info related to the primary. Like a customer database with address, state, phone, etc following the name of the customer.

But what happens in my case when the primary is followed by column that have more than one piece of data? For instance each primary keyword could have 300 derivations and another 100 catagories. I could use some help with laying out the structure this should take.

Likewise I would like to be able to build queries that will tell me when secondary columns data relates to the primary. For instance "yahoo email" will likely have data
in the catagory column that relates to "hotmail email" in the primary column.

Any help would be appreciated.

Sincerely,
Travis U.

 

[tracy1234]

Travis -
You may try tinkering with a table structure like this:

table = mytable
company   media    subject
yahoo     e-mail   box
yahoo     e-mail   password
yahoo     e-mail   hack
hotmail   e-mail   box

You lost me on the part about related categories. I would think that a simple query such as

select * from mytable
where media = 'e-mail';

would produce results that match yahoo e-mail to hotmail e-mail.

If you'd like to slim down the table, you could have other tables holding your company names, and your media types and then just reference them in mytable.

 

[travisdu]

Dear Tracy,

Thanks for responding, it got me thinking.

Actually maybe I didnt explain it quite fully. When I
say keywords I mean actual phrases that people enter
into the various search engines. So it would not fit
the company model for the table you suggested.

But it did get me to thinking about it and how about
a structure like this...

primary table = primary keywords
Key Keywords
ID # computer virus
ID # arthritis pain

table = keyword_derivations
Key Derivation_1 Derivation_2 Derivation_3-300....etc
ID # computer virus app computer virus help
ID # arthritis foot pain arthritis hand pain

table = similar catagories
Key Catergory_1 Catergory_ 2 etc...
ID # computer hacker virus scanners
ID # arthritis creams inflammatory conditions

Would that work?

What I would need to know after getting the structure
right is would it be possible to then run a query that
would find instances when keyword phrases in the "similar
catagories" table match an existing keyword phrase from
the "primary keywords table"?

Travis U.

 

[tracy1234]

Travis -

You're proposed table structure is going to cause you troubles. You should not have fields like category_1, category_2, category_3. You should simply have category.

Table structure would look like this:

keywords table:
keyword_id    keywords
1             computer virus
2             arthritis pain

category table:
keyword_id     category
1              computer hacker
1              virus scanners
2              arthritis cream
2              inflammatory conditions

select category
from keywords table 'kt', category table 'ct' 
where kt.keyword_id = ct.keyword_id;

That said, I have some questions -
What is the goal of the project?
Have you mapped out basics like purpose, data flow, inputs, outputs, etc?
How will you control user keyword inputs to ensure that they exactly match your table keywords?
How does the keyword derivations table come into play?

And a recommendation-
Search on the phrase "normalization" in the Access forums. This will give you an idea as to standard table structures.

-Tracy