Creating table relationships

[mikeytj]

Hi,

I have problems getting my head around the whole concept of what information to put in which tables with links and so on. My kids would say that my joke about having wooden teeth and grey hair has something to do with it. Prob right.

I have two access DB apps running right now. The first is just one table and the web app searches and displays the info fine and not in question.

DB two is one I'm doing now and more complicated. I have many questions so not sure this is where to put them or via email.

As for what to put in tables and so forth it is working fine now with two tables relating to job listings.

Table one (the only table today that has variables)has the following columns

active (has y or n only for active or inactive)
city
state
zip
jobid

Table two has quite a number of columns that are the same for each of the variants in table one. The web app goes to table one and if the y or n col is y then extracts the info from all other columns and the the required columns in table two for that app. It then generates a RSS or XML file as needed for various services to crawl.

This provides a listing of a large number of cities in the US that I can hire in for one particular type of job and is finally working fine.

Now comes my delima. I now am getting to where I have a variety of other jobs that will have for example different descriptions, pay structures, summaries, etc., while still using cities, state, zip from the first table and other info from the second.

There will be about 6 different job types which I plan to add a col to table one for.
The existing table two has a number of fields that will apply to all jobs and or outputs, such as benefits, summaries, etc.

The issue comes in that I now will have a large number of descriptions. The existing one fits 1700+ jobs. Another will fit maybe 20 and another maybe 12. Then comes the descriptions that are unique to only one job id within one job type (there will be a couple of hundred of these when I'm done).

When I first built the db I had the main table with 5 fields/columns and the second table with about 15. The db size with only on record in each table was a whopping 5+MB. The main table now has 1700+ entries times 5 records each and stil only one record in the second and I'm at 10MB. The number of users is not a factor as the program runs once upon command, extracts the information and puts it in the proper format for the specified XML file. Web users in this app only have an impact on the db when they click a link on a posted notice which then pulls the exact information related to that job and builds a page for the viewer to see.

So in a long version you now have my situation and I need to get all these other jobs entered in the most efficient manner for the web app to work and extract them. Logic tells me that instead of two tables I'll now need three or more. I can use the asp program to go to each individual table base on the basic parameters but I somehow feel there is a better way to do it. With no concept( it is a huge block I have) on the one to one, one to many, related ID's vs foriegn id's I'm stuck as to how to move on.

Any assistance would be greatly appreciated. Pictures would be great LOL.

I am available via email if you wish.

Thanks for putting up with the long post.

Mike

[MMcCarthy]

Mike I am moving this into theAccess Forum as it is a question. However, this tutorial has all the information you need. I can't actually build your structure for you. If there is some part of the tutorial you can't understand I'll be happy to clarify it for you.

[mikeytj]

Thanks for the quick reply. Right now I'm trying to understand if I need to set up multiple tables that have some common field name (say jobtype) and create a relationship or just multiple tables with the variant information based on jobtype then use the asp to do if thens and extract the data. I know your tutuorial is great I just don't understand enough about the principles of relationships and if I make them what does that do for me on the programming end to make thing better or worse. I know you can't build my db for me but maybe you can give an alternate picture or comic book type source LOL that would get throught this block I have.....

I've managed to break it down to a main or primary table with the fields I listed before and plan to add Jobtype of which there are 5 at this time.

The current second table has most of the fields that will not change for any of the job types. Some will now differ based on the new job types. It does not make sense to me to have all 15 or so fields copied everytime I make a record for a seperate job where only say five of the fields differ. (both for input productivity and db size)

I think I should add a table now for each of the job types to each have the fields that are the same name, that would have different content. So for example if I have a jobtype of Regional in table one, and a jobcode of seregatlanta with if then statements I could get city, state, zip out of table 1, all standard fields from table two, then go to the "Regional" table, then to the jobcode and extract the specifics from the fields that would be different.

Where I am confused on the one to one, and many to many etc is "Will setting relationships, etc as described do me any good from the progamming end?" Will I be able to shorten the code by any amount or ??

Sorry just not getting it enough to know if I need to set it up or not.

Thank you for your patience and response.

Mike