help on related tables and query, all info within :)
Alright guys, i'm still not exactly the best at this database design stuff, but i'm really working on it! Trying to get better, I need to learn from more experienced people :)
Anyways, here's my problem. Look at my attached file.
I've got my main obituary table which contains all the information required for an obituary. I'm basing everything off the obit_id field in the obituary table. This is where my problem comes in. I can get rid of the obit_visitationID,obit_serviceID, and obit_cemetarID fields because i'm not going to use them.
I've got a location table that holds location information for the 3 different kinds of ceremonies involved in one's death. It's defined by the loc_type which are "Cemetary" "Visitation" and "Service". I wanted to keep all of these locations in a table so it would be easy to manage them and the people using this app would just have one place to update locations and stuff.
For example, when I pull a query for obit_id = 1, when it hits the visitation table, i want it to look up the loc_id in the location table and return it in the query. Also, when it gets to the cemetary table I'd like it to pull out the information in the location table inside the same query, as well as the service location information.
I hope i'm getting my point across clearly. Basically if I have a different (or same) location for each visitation, cemetary, or service, i'd like for the location information to be pulled up 3 times.
Is it possible to do this in one query? Should I change the relationships in my databaes? The relationships from the obituary table to the visitation, cemetary, and service tables are one to one because there is only one service, cemetary, and visitation entry per obituary. Would I have to do 3 seperatre queries saying something like SELECT loc_name,loc_address,etc.. FROM location WHERE location.loc_id = visitation.loc_id AND visitation.loc_id = #obit number#?
I'm kind of clueless. I don't want to have to run like 3 queries because that would degrade the performance of my server right? (It's MS SQL Server 2000) Or is that the only way to get this done?
Thank you guys so much for the help!
E-mail me @ email@example.com or IM me on AOL with cyrixsc if you have any questions.
Once again thanks!!