Normalization / Referential Integrity
-
I am using a mySql DB and I am actually finding that I am having a problem in the scope of my application. Here is my dilemma: I have to track data about homes and about their relative owners. I have, at this point, created two tables, one called RealProperty and one called HomeOwner. The data for the property needs to be accessed regularly, where the data about the owner is going to be used a lot less frequently. So I decided to split it into two tables because I wanted to keep the RealProperty table as lean as possible to ensure speed of data access. To the root of it then: 1. Is it a smart move to separate this into two tables? There will always be a 1:1 relationship between the two tables. 2. How do I guarantee referential integrity between the two tables? I currently have an Owner ID field in the property table that references the Id field in the homeowner table, I really want to cascade my insert so that there is a home owner record created whenever a property record created. Should I just bite the bullet and put a trigger after insert on the property table? Is there a better way to handle this? The end product of this is that I have a windows form (Wpf actually) where the user can view and edit the homeowner and property information on the screen and I want to make sure that it is seemless (No add homeowner button or anything of the sort). So to sum up: 1. Is it the best approach to separate this data into two tables? 2. Is there a better approach for this than an insert trigger on the property table? Cheers, --EA
-
I am using a mySql DB and I am actually finding that I am having a problem in the scope of my application. Here is my dilemma: I have to track data about homes and about their relative owners. I have, at this point, created two tables, one called RealProperty and one called HomeOwner. The data for the property needs to be accessed regularly, where the data about the owner is going to be used a lot less frequently. So I decided to split it into two tables because I wanted to keep the RealProperty table as lean as possible to ensure speed of data access. To the root of it then: 1. Is it a smart move to separate this into two tables? There will always be a 1:1 relationship between the two tables. 2. How do I guarantee referential integrity between the two tables? I currently have an Owner ID field in the property table that references the Id field in the homeowner table, I really want to cascade my insert so that there is a home owner record created whenever a property record created. Should I just bite the bullet and put a trigger after insert on the property table? Is there a better way to handle this? The end product of this is that I have a windows form (Wpf actually) where the user can view and edit the homeowner and property information on the screen and I want to make sure that it is seemless (No add homeowner button or anything of the sort). So to sum up: 1. Is it the best approach to separate this data into two tables? 2. Is there a better approach for this than an insert trigger on the property table? Cheers, --EA
-
I am using a mySql DB and I am actually finding that I am having a problem in the scope of my application. Here is my dilemma: I have to track data about homes and about their relative owners. I have, at this point, created two tables, one called RealProperty and one called HomeOwner. The data for the property needs to be accessed regularly, where the data about the owner is going to be used a lot less frequently. So I decided to split it into two tables because I wanted to keep the RealProperty table as lean as possible to ensure speed of data access. To the root of it then: 1. Is it a smart move to separate this into two tables? There will always be a 1:1 relationship between the two tables. 2. How do I guarantee referential integrity between the two tables? I currently have an Owner ID field in the property table that references the Id field in the homeowner table, I really want to cascade my insert so that there is a home owner record created whenever a property record created. Should I just bite the bullet and put a trigger after insert on the property table? Is there a better way to handle this? The end product of this is that I have a windows form (Wpf actually) where the user can view and edit the homeowner and property information on the screen and I want to make sure that it is seemless (No add homeowner button or anything of the sort). So to sum up: 1. Is it the best approach to separate this data into two tables? 2. Is there a better approach for this than an insert trigger on the property table? Cheers, --EA
I'd also add to the above comment to create a RealProperty view that queries on only those columns that deal with RealProperty and like wise for the HomeOwner data as well. Just make sure that each view uses the index fields defined at the table level and everything should work fine. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
I am using a mySql DB and I am actually finding that I am having a problem in the scope of my application. Here is my dilemma: I have to track data about homes and about their relative owners. I have, at this point, created two tables, one called RealProperty and one called HomeOwner. The data for the property needs to be accessed regularly, where the data about the owner is going to be used a lot less frequently. So I decided to split it into two tables because I wanted to keep the RealProperty table as lean as possible to ensure speed of data access. To the root of it then: 1. Is it a smart move to separate this into two tables? There will always be a 1:1 relationship between the two tables. 2. How do I guarantee referential integrity between the two tables? I currently have an Owner ID field in the property table that references the Id field in the homeowner table, I really want to cascade my insert so that there is a home owner record created whenever a property record created. Should I just bite the bullet and put a trigger after insert on the property table? Is there a better way to handle this? The end product of this is that I have a windows form (Wpf actually) where the user can view and edit the homeowner and property information on the screen and I want to make sure that it is seemless (No add homeowner button or anything of the sort). So to sum up: 1. Is it the best approach to separate this data into two tables? 2. Is there a better approach for this than an insert trigger on the property table? Cheers, --EA
eddieangel wrote:
There will always be a 1:1 relationship
I would not count on that. Besides, as a developer, you would be better off learning to handle the more general situation sooner rather than later. I would take a slightly different tack and either have the RealProperty table refer to the HomeOwner table (as it's more common for one owner to own multiple properties) or (more likely) allow many-to-many relationships via a third table. But that's just me. I would not use a trigger. In the DAL, I would have a method to add a property and a method to add an owner -- and another method to add a relationship. In the API I would have a method that calls both (all three) and handles transactioning. I would also use SQL Server :-D . Edit: Fixed relationship :doh: . Edit 2: Oh, yeah, you had it that way... :doh: :doh: :doh:
modified on Monday, October 18, 2010 3:20 PM
-
I am using a mySql DB and I am actually finding that I am having a problem in the scope of my application. Here is my dilemma: I have to track data about homes and about their relative owners. I have, at this point, created two tables, one called RealProperty and one called HomeOwner. The data for the property needs to be accessed regularly, where the data about the owner is going to be used a lot less frequently. So I decided to split it into two tables because I wanted to keep the RealProperty table as lean as possible to ensure speed of data access. To the root of it then: 1. Is it a smart move to separate this into two tables? There will always be a 1:1 relationship between the two tables. 2. How do I guarantee referential integrity between the two tables? I currently have an Owner ID field in the property table that references the Id field in the homeowner table, I really want to cascade my insert so that there is a home owner record created whenever a property record created. Should I just bite the bullet and put a trigger after insert on the property table? Is there a better way to handle this? The end product of this is that I have a windows form (Wpf actually) where the user can view and edit the homeowner and property information on the screen and I want to make sure that it is seemless (No add homeowner button or anything of the sort). So to sum up: 1. Is it the best approach to separate this data into two tables? 2. Is there a better approach for this than an insert trigger on the property table? Cheers, --EA
I do believe your data structure is incorrect. Your constraint of 1:1 between owner and property fails when someone own more that 1 property or a property has more than 1 owner or a property is sold (I assume you wish to retain history of the property). As Piebald suggested a many:many link table is what you need to implement. What happens if you have a company own the property, now you are in the position of needing an address table so you need a many:many link between owner and address... Designing a data structure is not a trivial excercise and needs time and effort to get it right. It is guaranteed that you will not get it right the first time. Be prepared to admit an error and redesign ASAP, allowing an incorrect design to persist only deepens the cost of fixing the problem.
Never underestimate the power of human stupidity RAH
-
I do believe your data structure is incorrect. Your constraint of 1:1 between owner and property fails when someone own more that 1 property or a property has more than 1 owner or a property is sold (I assume you wish to retain history of the property). As Piebald suggested a many:many link table is what you need to implement. What happens if you have a company own the property, now you are in the position of needing an address table so you need a many:many link between owner and address... Designing a data structure is not a trivial excercise and needs time and effort to get it right. It is guaranteed that you will not get it right the first time. Be prepared to admit an error and redesign ASAP, allowing an incorrect design to persist only deepens the cost of fixing the problem.
Never underestimate the power of human stupidity RAH
I appreciate the insight. Thank you.
-
I am using a mySql DB and I am actually finding that I am having a problem in the scope of my application. Here is my dilemma: I have to track data about homes and about their relative owners. I have, at this point, created two tables, one called RealProperty and one called HomeOwner. The data for the property needs to be accessed regularly, where the data about the owner is going to be used a lot less frequently. So I decided to split it into two tables because I wanted to keep the RealProperty table as lean as possible to ensure speed of data access. To the root of it then: 1. Is it a smart move to separate this into two tables? There will always be a 1:1 relationship between the two tables. 2. How do I guarantee referential integrity between the two tables? I currently have an Owner ID field in the property table that references the Id field in the homeowner table, I really want to cascade my insert so that there is a home owner record created whenever a property record created. Should I just bite the bullet and put a trigger after insert on the property table? Is there a better way to handle this? The end product of this is that I have a windows form (Wpf actually) where the user can view and edit the homeowner and property information on the screen and I want to make sure that it is seemless (No add homeowner button or anything of the sort). So to sum up: 1. Is it the best approach to separate this data into two tables? 2. Is there a better approach for this than an insert trigger on the property table? Cheers, --EA
I agree with the statement that I think your reasoning is flawed. More often then not you are going to see multiple owners to a home (or any other entity) simply because of things like marriage, partnerships, trusts, etc... Also, keeping history now requires that you set up 2 tables that contain the same data linked to a building. Keeping it all in one table and adding an attribute to show current vs. past ownership I think is the better option. In fact, you are probably really going to want to separate out the table that tracks the data about the building, the tables that track the address data of all the possible owners and then use a join table to crate the linking between the two. That I think is the best way to achieve a clean design. When you consider what else you can store in this third reference table (IE: the data about the sales agent(s) involved, the inspectors used durring the sales, etc...) you start to see how it works cleaner.