Several Questions: Relationships and SSMS and Data Diagrams
-
I'm struggling setting relationships among tables. I understand the one-to-many, many-to-one, many-to-many, and self-referential concepts. It's implementing them that I find a bit vexing.
I've used queries like this successfully:
ALTER TABLE clan.parents
ADD CONSTRAINT FK_FatherID_PeopleID FOREIGN KEY (parentsFatherId)
REFERENCES clan.people (peopleID);
How can I view relationships after I've completed queries like the above? After that query I started a new database diagram and got the ss below, seeming to show a self-referential relationship. In SQL terms clan.parents in the query is the child or subordinate table and the clan.people table is the parent or superior table. IOW clan.people is the main table in my database that relates to all the other tables as children. Have I done something wrong in the query?
I've also used Data Diagrams to select a column in one table and drop it in a column in another table, but the lines drawing links puzzle me because they don't actually get specific as to column in each table. Either I'm doing something wrong or I don't understand the process (or both). For example, I tried to set the relationship set in the query in a data diagram (before entering the query). I got a result but when I hovered over the link line I got information like the ss below. I don't know how to interpret that information. It appears to be telling me that I linked the 2 tables without telling me which columns were linked.
I think the ['FK_people_parents (clan)'] segment is a descriptor generated by SQL Server and is equivalent to "FK-FatherID_PeopleID" in the query above. The remainder of the information [between 'parents(clan)' and 'people (clan)'] tells me only that the tables are linked without telling what columns are used. There no information equivalent to "FOREIGN KEY (parentsFatherId) REFERENCES clan.people (peopleID)" in the query.
Is this because I'm using the Data Diagram tool wrong?
- This topic was modified 1 month ago by Ahr Aitch.
Be kind. Be calm. Be generous. Behave.
-
Adding to my confusion (see previous post), I selected clan.parents table then right clicked and chose Relationships. I don't understand what the window in the ss tells me. I never created a FK_parents_parents (at least not that I'm aware of). The window (I think) says that a relationship exists in the table (but which table is not identified explicitly, that I see); and the window does not indicate what columns are 'bound' in the relatonship.
????
Be kind. Be calm. Be generous. Behave.
-
I don't understand the implementation/value of clan.parents. If you are only modeling biological parents, I would think would simply have a motherID & fatherID on clan.person, which is a self-reference to peopleID. If you are allowing for step-parents, adopted parents, etc., I dont see a column to differentiate those cases.
Even if you needed/wanted clan.parents table for some reason, the inclusion of name columns seems redundant & prone to unintended differences from their name in clan.person... unless you are trying to allow for their names at the time of birth of the child to differ from current names.
Even then, that usage isn't clear (is it name at child's birth or some other period?), and might not be sufficient. I might be more inclined to have a name history/audit table with a datetime to track name changes over time.
-
OK, after closer inspection I found that I could reveal more information about keys (ss below), but it adds to my confusion. All of the keys seem to reference the same table (which explains why the diagram shows the relationship as self-referential. So, I must have done something wrong in the query.
Repeating the query:
ALTER TABLE clan.parents
ADD CONSTRAINT FK_FatherID_PeopleID FOREIGN KEY (parentsFatherId)
REFERENCES clan.people (peopleID);
In the first line, ALTER TABLE clan.parents says to change something in the table by that name, no? In the second line, ADD CONSTRAINTS says to tie the change to FK_FatherID_PeopleID, a phrase I made up to remind me of the columns linked, no? Continuing in the second line, FOREIGN KEY (parentsFatherID) says the column by that name is the linked column, no?
In the third line, REFERENCES clan.people (peopleID) names the column in peopleID in table clan.people as the column linked to parentsFatherID, no?
Where did I misinterpret the syntax?
Be kind. Be calm. Be generous. Behave.
-
Thanks for the response ratbak.
The design might indeed be poor as I am very inexperienced with RDBMS, being an old (83) COBOL programmer accustomed to flat files.
I chose to create a table of parent name values because there will be entries for multiple children in the clan.people table, sometimes more than 12. Putting parent names in each record for offspring would duplicate those parent names, exposing the risk of error in recording them. Taking it a step further, when grandchildren and great grandchildren are added, there would be even more duplication and chance of error.
I take your point about having names of parents in the clan.people table. Put that down to poor thinking and inability to see ones own errors in judgment.After reviewing my tables, I see that I did not include the names of parents in the clan.people table. At one point I had them there then decided to have a separate table. I had not thought of step-parents and adoptive parents. I'll have to give that some thought and incorporate that into the design. As for name changes, I see only the need to accommodate changes that occur by marriage. I have another table, clan.spouses, for that and I use maiden names for women and will have code to append spouse last names for marriages, also in another table, clan.marriages.I think my naming convention might be adding confusion. Because I'm old and expect to see datanames in various places throughout HTML and javascript and php, I wanted a mnemonic to tell me what table the datanames are defined in. To that end I chose to prefix all datanames with the name of the table, thus ID for entries in the clan.people table becomes peopleID and birthdate become people.birthdate etc. Although at present, I'm working only on code for the clan schema that will relate to my parents' generation, I anticipate another schema for ancestors and even another for descendants. As I said, I'm new at this and learning while I add and discard ideas.
Far better to catch those oversights now when there is no data yet populating the tables.
While I greatly appreciate the critique of the design, my reason for the post is to learn what I have not mastered about setting relationships, as you will see in the other follow up posts in this thread.
Be kind. Be calm. Be generous. Behave.
-
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply