If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
confusing relationships
Thanks David for your reply. I have just seen and posted my reply to Vincent,
i will try to do the things that you guys suggested. David M C wrote: Your contact table would have an AddressID field. You would then have another table containing all the address information. This would also have an AddressID field which will be the primary key. You then create a relationship between the two tables (open the relationships window, add the two tables and drag one AddressID field on to the other, click "enforce referential integrity". Each contact in the same household would have the same AddressID. Dave Please see inline.. [quoted text clipped - 41 lines] kingnothing -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200603/1 |
#22
|
|||
|
|||
confusing relationships
Yes Tom,
I will implement the suggested design from Vincent and put the screenshot of the final design on the web for others to see. Thanks, kingnothing Tom Ellison wrote: Dear Kingnothing: I have been looking at this site. Would you consider making it more useful? Please make the width and height of the table rectangles large enough to be able to see the width and height for all the columns and their names. What I said about combining contact, spouse, and child into a single table still stands, tentatively, as I have not really seen your design yet. Please reveal it as I requested and post back here if you're interested. Tom Ellison Yeah i know about referential intergrity. so, you are saying that relationships are absolutely imperative for the database design, right!! [quoted text clipped - 32 lines] kingnothing -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200603/1 |
#23
|
|||
|
|||
confusing relationships
This might seem a little dumb (Ever after you explainin in such detail) but
here goes... This question has always plagued me for ages... Do you do the relationships in the tbles (as in right click on tables section and do the relationships) or do you leave the tables there and create a query in design view, add all the tables, and create relationships there?? I'm talking about the major relationships that are a part of your design... Like for example, in your answer, do i just create tables and forget abt the relationships and then create a select query and do all the relationships there??? I dont get it... PS: I will put up the db once it is done...so that other people can have easy access to it and learn from it.. Help Vincent!! Regards, kingnothing Vincent Johns wrote: Great! As I may have mentioned, there's no one perfect way to design your database, but trying to avoid duplicating stored information, using meaningful names, etc., can save you a lot of work in the future. Good luck. Although others may differ with me on this, I think it's a good idea to get your Tables and Queries working first, to be sure you've covered everything you need (for now), and then, after they're working well, to build your Forms (for data display and input by users) and Reports (for data display and printouts) based on the Queries. Some people say that you should never use Table Datasheets for entering data, but I think that that's not harmful, and will save time, while you're trying to set things up. Later, before you give your users access to the database, you should define Forms that can provide a better user interface. If you post a sanitized version (= without any real personal data that might be of a sensitive nature) of your database on your Web site, so that people could download it, that might save some work. Or at least, maybe you could post example records from your Tables. -- Vincent Johns Please feel free to quote anything I say here. Thanks a lot Vincent, I really appreciate all the work that has gone into this reply. It is really helping me a lot. [quoted text clipped - 10 lines] did not do so with the [Person] Table, because it interfered with adding new records. [...] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200603/1 |
#24
|
|||
|
|||
confusing relationships
Dear Vincent:
Create relationships in the relationship diagram. Start with a drag and drop between the related columns in the two tables involved. As I have seen your diagram, I would think this would be familiar if you have done the work so far. Tom Ellison "kingnothing via AccessMonster.com" u18754@uwe wrote in message news:5ddc5d11d9439@uwe... This might seem a little dumb (Ever after you explainin in such detail) but here goes... This question has always plagued me for ages... Do you do the relationships in the tbles (as in right click on tables section and do the relationships) or do you leave the tables there and create a query in design view, add all the tables, and create relationships there?? I'm talking about the major relationships that are a part of your design... Like for example, in your answer, do i just create tables and forget abt the relationships and then create a select query and do all the relationships there??? I dont get it... PS: I will put up the db once it is done...so that other people can have easy access to it and learn from it.. Help Vincent!! Regards, kingnothing Vincent Johns wrote: Great! As I may have mentioned, there's no one perfect way to design your database, but trying to avoid duplicating stored information, using meaningful names, etc., can save you a lot of work in the future. Good luck. Although others may differ with me on this, I think it's a good idea to get your Tables and Queries working first, to be sure you've covered everything you need (for now), and then, after they're working well, to build your Forms (for data display and input by users) and Reports (for data display and printouts) based on the Queries. Some people say that you should never use Table Datasheets for entering data, but I think that that's not harmful, and will save time, while you're trying to set things up. Later, before you give your users access to the database, you should define Forms that can provide a better user interface. If you post a sanitized version (= without any real personal data that might be of a sensitive nature) of your database on your Web site, so that people could download it, that might save some work. Or at least, maybe you could post example records from your Tables. -- Vincent Johns Please feel free to quote anything I say here. Thanks a lot Vincent, I really appreciate all the work that has gone into this reply. It is really helping me a lot. [quoted text clipped - 10 lines] did not do so with the [Person] Table, because it interfered with adding new records. [...] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200603/1 |
#25
|
|||
|
|||
confusing relationships
Thanks Vincent,
Thats what i meant, you can achieve Tools -- Relationships by right clicking on an empty space in the sections where the tables live. I am in the process of completely revamping the design, i will let you know how i go Regards, kingnothing Vincent Johns wrote: Tom Ellison's advice is good, but actually you can create default relationships in the Relationships window, and you can override them in the Query Design View window. In some cases, you might even get by without defining anything at all in the Relationships window -- but, like Tom, I suggest that you set up as much as you can there first. If you do so, then when you define Queries based on the related Tables, the relationships will magically appear in Query Design View (saving you some work). You can then alter or delete them, or add new ones, in Query Design View (but you won't be able to specify Referential Integrity there). To answer your question, yes, you do need to define Tables, including at least some of the fields in them, before you can define relationships among those fields. Incidentally, I'm not sure what you mean by "right click on tables section". In my versions of Access, the Relationships window is accessed via the Tools -- Relationships menu, and you can add any or all of the Tables in your database to that window. This might seem a little dumb (Ever after you explainin in such detail) but here goes... [quoted text clipped - 50 lines] [...] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200603/1 |
#26
|
|||
|
|||
confusing relationships
Now, I would not have called a JOIN created in a query a relationship.
Relationships are part of table design and are enforced. JOINs can be made on enforced relationships or not. Relationships are always based on columns in two different tables that are exactly identical. Queries can use non-equi JOINs that are in no way relationships. So, while the two overlap in many cases, they are not equivalent. Tom Ellison "kingnothing via AccessMonster.com" u18754@uwe wrote in message news:5dddaa133141e@uwe... Thanks Vincent, Thats what i meant, you can achieve Tools -- Relationships by right clicking on an empty space in the sections where the tables live. I am in the process of completely revamping the design, i will let you know how i go Regards, kingnothing Vincent Johns wrote: Tom Ellison's advice is good, but actually you can create default relationships in the Relationships window, and you can override them in the Query Design View window. In some cases, you might even get by without defining anything at all in the Relationships window -- but, like Tom, I suggest that you set up as much as you can there first. If you do so, then when you define Queries based on the related Tables, the relationships will magically appear in Query Design View (saving you some work). You can then alter or delete them, or add new ones, in Query Design View (but you won't be able to specify Referential Integrity there). To answer your question, yes, you do need to define Tables, including at least some of the fields in them, before you can define relationships among those fields. Incidentally, I'm not sure what you mean by "right click on tables section". In my versions of Access, the Relationships window is accessed via the Tools -- Relationships menu, and you can add any or all of the Tables in your database to that window. This might seem a little dumb (Ever after you explainin in such detail) but here goes... [quoted text clipped - 50 lines] [...] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200603/1 |
#27
|
|||
|
|||
confusing relationships
Hi Vincent, and all of you...thank you very much for helping me through. The
concepts have finally got into my head. I have designed just the basic stuff and have put a screen shot here -- http://members.westnet.com.au/mukund/rel1.jpg Person_1 == Inner Join for Spouse Person_2 == Inner Join for Child I hope everything is alright with this.... I could not enforce ref int as it was not allowing me to add data. I realise this is just the begining....i will still need your help for the later stages Regards, Vincent Johns wrote: Great! As I may have mentioned, there's no one perfect way to design your database, but trying to avoid duplicating stored information, using meaningful names, etc., can save you a lot of work in the future. Good luck. Although others may differ with me on this, I think it's a good idea to get your Tables and Queries working first, to be sure you've covered everything you need (for now), and then, after they're working well, to build your Forms (for data display and input by users) and Reports (for data display and printouts) based on the Queries. Some people say that you should never use Table Datasheets for entering data, but I think that that's not harmful, and will save time, while you're trying to set things up. Later, before you give your users access to the database, you should define Forms that can provide a better user interface. If you post a sanitized version (= without any real personal data that might be of a sensitive nature) of your database on your Web site, so that people could download it, that might save some work. Or at least, maybe you could post example records from your Tables. -- Vincent Johns Please feel free to quote anything I say here. Thanks a lot Vincent, I really appreciate all the work that has gone into this reply. It is really helping me a lot. [quoted text clipped - 10 lines] did not do so with the [Person] Table, because it interfered with adding new records. [...] -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200603/1 |
#28
|
|||
|
|||
confusing relationships
I do have a couple of comments about your design.
Of course, you may name your fields whatever you wish, but I usually give a primary key the same name as its Table, followed by "_ID" (and avoid ending non-key fields with "ID"), and you are already doing that. In addition, I give each foreign key a name that either matches that of the matching primary key, or if there are two or more such in the same Table, a name that ends with the primary key's name. So, instead of key names like [Insurance].[Insurance_Person_ID], I would use something more like [Insurance].[Contact_Person_ID], [Insurance].[Spouse_Person_ID], or [Insurance].[Parent_Person_ID]. You already know that these foreign keys are located in the [Insurance] Table, so I think it's not necessary to repeat that information. The "Person_ID" part of each name tells you that the key is supposed to match the primary key in the [Person] Table. More significant than the names you choose (though I think well-chosen names can help considerably) is what each field means, how it relates to the real world. What is the meaning of your [Insurance].[Insurance_Child_ID] field? Since there is only one such field in each [Insurance] record, I assume that it represents the (unique) person who is a child who is associated with that policy; i.e., one policy in that Table cannot be associated with two children. Is this what you want? Is it possible for there to be a record in [Insurance] in which both the [Insurance].[Insurance_Child_ID] and [Insurance].[Insurance_Spouse_ID] fields have non-null values? This would mean that one policy covers both a spouse and a child, I suppose, even though it cannot cover a spouse and two children. If you don't want both fields to have non-null values in the same record, you can write a Query which looks for that, but there might be an easier way to enforce that restriction. (Since I don't know what your business rules are, I'm just guessing here, but here is an area where clearly named fields can call attention to places where they are used inconsistently.) I have the same comment about the [Address] Table. Are you sure that you want a one-child policy enforced for addresses, as you have done here? There would, however, be no problem that I can see with having both a spouse and a child attached to a given address. My guess is that some of your links are backward -- for example, putting a [Person].[Address_ID] into each [Person] record would allow you to attach an address to everyone (even children), though you could leave some empty to indicate, for example, that your last letter there was returned as undeliverable. As you have it, you can list several addresses for each child, or several insurance policies for a given spouse, but I'm not convinced that that makes sense. -- Vincent Johns Please feel free to quote anything I say here. kingnothing via AccessMonster.com wrote: Hi Vincent, and all of you...thank you very much for helping me through. The concepts have finally got into my head. I have designed just the basic stuff and have put a screen shot here -- http://members.westnet.com.au/mukund/rel1.jpg Person_1 == Inner Join for Spouse Person_2 == Inner Join for Child I hope everything is alright with this.... I could not enforce ref int as it was not allowing me to add data. I realise this is just the begining....i will still need your help for the later stages Regards, Vincent Johns wrote: Great! As I may have mentioned, there's no one perfect way to design your database, but trying to avoid duplicating stored information, using meaningful names, etc., can save you a lot of work in the future. Good luck. Although others may differ with me on this, I think it's a good idea to get your Tables and Queries working first, to be sure you've covered everything you need (for now), and then, after they're working well, to build your Forms (for data display and input by users) and Reports (for data display and printouts) based on the Queries. Some people say that you should never use Table Datasheets for entering data, but I think that that's not harmful, and will save time, while you're trying to set things up. Later, before you give your users access to the database, you should define Forms that can provide a better user interface. If you post a sanitized version (= without any real personal data that might be of a sensitive nature) of your database on your Web site, so that people could download it, that might save some work. Or at least, maybe you could post example records from your Tables. -- Vincent Johns Please feel free to quote anything I say here. Thanks a lot Vincent, I really appreciate all the work that has gone into this reply. It is really helping me a lot. [quoted text clipped - 10 lines] did not do so with the [Person] Table, because it interfered with adding new records. [...] |
#29
|
|||
|
|||
confusing relationships
I'm Back,
This time, i have the completed product with me. I thank all of you for providing invaluable help. Find it here -- http://members.westnet.com.au/mukund/insurance.zip This is no way the finished product, but i'm nearly there. I need to do a fair bit with regards to reports and stuff, also have included a switchboard (autogenerated) just for the heck of it. Please review it and let me know... Thanks kingnothing Vincent Johns wrote: I do have a couple of comments about your design. Of course, you may name your fields whatever you wish, but I usually give a primary key the same name as its Table, followed by "_ID" (and avoid ending non-key fields with "ID"), and you are already doing that. In addition, I give each foreign key a name that either matches that of the matching primary key, or if there are two or more such in the same Table, a name that ends with the primary key's name. So, instead of key names like [Insurance].[Insurance_Person_ID], I would use something more like [Insurance].[Contact_Person_ID], [Insurance].[Spouse_Person_ID], or [Insurance].[Parent_Person_ID]. You already know that these foreign keys are located in the [Insurance] Table, so I think it's not necessary to repeat that information. The "Person_ID" part of each name tells you that the key is supposed to match the primary key in the [Person] Table. More significant than the names you choose (though I think well-chosen names can help considerably) is what each field means, how it relates to the real world. What is the meaning of your [Insurance].[Insurance_Child_ID] field? Since there is only one such field in each [Insurance] record, I assume that it represents the (unique) person who is a child who is associated with that policy; i.e., one policy in that Table cannot be associated with two children. Is this what you want? Is it possible for there to be a record in [Insurance] in which both the [Insurance].[Insurance_Child_ID] and [Insurance].[Insurance_Spouse_ID] fields have non-null values? This would mean that one policy covers both a spouse and a child, I suppose, even though it cannot cover a spouse and two children. If you don't want both fields to have non-null values in the same record, you can write a Query which looks for that, but there might be an easier way to enforce that restriction. (Since I don't know what your business rules are, I'm just guessing here, but here is an area where clearly named fields can call attention to places where they are used inconsistently.) I have the same comment about the [Address] Table. Are you sure that you want a one-child policy enforced for addresses, as you have done here? There would, however, be no problem that I can see with having both a spouse and a child attached to a given address. My guess is that some of your links are backward -- for example, putting a [Person].[Address_ID] into each [Person] record would allow you to attach an address to everyone (even children), though you could leave some empty to indicate, for example, that your last letter there was returned as undeliverable. As you have it, you can list several addresses for each child, or several insurance policies for a given spouse, but I'm not convinced that that makes sense. -- Vincent Johns Please feel free to quote anything I say here. Hi Vincent, and all of you...thank you very much for helping me through. The concepts have finally got into my head. [quoted text clipped - 45 lines] [...] -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Moving Relationships Between Databases | kh | Running & Setting Up Queries | 4 | February 23rd, 2006 05:46 PM |
Using Relationships window | [email protected] | Database Design | 11 | October 2nd, 2005 06:44 PM |
Confused about one-to-many or many-to-many relationships | CAD Fiend | Database Design | 4 | July 7th, 2005 03:38 PM |
Importing Tables/Missing Relationships | Elena | Running & Setting Up Queries | 1 | May 20th, 2005 12:43 AM |
Mixed up with Relationships..help! | KrazyRed | New Users | 3 | January 26th, 2005 05:03 AM |