Help With Database Design
-
Hi, I am designing a database for keeping records of properties purchased and sold. And I am not sure how to implement the following relationship as shown in the screenshot below. I think this is somewhat related to the concept of inheritance but I am not sure how to best implement it at the database level.
One Sales Deed has many buyers and sellers. The difficulty however is that both buyer and seller can be an Individual Person, A Company, A Trust, or some other entity. And each of these has some common and different attributes.
I would like to know what is the best way to implement this. If you can please show with TSQL commands that would be very helpful.
This is the best I can describe it, but if you need more information please let me know.
Any help will be greatly appreciated!
Thank you.
- This topic was modified 1 year, 3 months ago by rgarayia.
-
Can you please elaborate on this statement: "One Sales Deed has many buyers and sellers."?
😎
Certainly, there is only a single property on each deed, and partial shares are defined within the deed/contract, colour me slow but what is the problem?
-
Please look at the post below:
-
Ok here is what I am trying to achieve and I have come up with a design. If someone can cast their eyes on the design and let me know if there are any issues with it, or if there is a better way to do it that would be greatly appreciated.
- One sale deed can contain one or more plots (pieces of land) for sale, and one plot can be sold a number of times.
- Each sale deed lists the names of one or more buyers and sellers along with their addresses. The addresses of buyers and sellers at the time of execution of the deed need to be retained in the database for historical reference.
- These buyers and sellers need not always be individual persons they can also be a Trust. For example, a Trust could sell plot(s) to another Trust or to individual person(s) and vice-versa.
To achieve the above requirements I have done the following design:
I hope that makes sense. Please feel free to ask any questions or clarifications.
Thank you.
-
That looks pretty good.
Police station is an interesting attribute of plot. It seems like that could change over time. Do you care about that historically, or only the current value?
Do you need to track if a plot is subdivided or combined/merged (plottage/assemblage)? Or are plots immutable?
If so, how would you do that?
-
If I'm understanding then imo there are possibly some non-modelled attributes and/or relationships. Also, when the spec says to record the address at the time of the transaction imo that means literally copying the row/column values and not just storing a foreign key reference. In the data model pictured the buyers and sellers are not identifiable. Imo, the correct table to contain buyers and sellers is 'Deed'. The "Entity" table(s) are too generic and so are not necessary. How to variably assign either a Person or a Trust to be (a) buyer(s) and/or seller(s)? Variations of this issue come up all the time. Imo, you need a DeedBuyers table and a DeedSellers table (each) with a surrogate primary key and a foreign key reference to the deed_number. These tables would contain 1 row for each buyer or seller (as appropriate) and also contain the point in time address information, i.e. the columns from Address table. Also imo, you could create a DeedBuyerPersons table and a DeedBuyerTrusts table to model that relationship. Similarly, you could create DeedSellerPersons and DeedSellerTrusts. By explicitly modeling these relationships it requires more complex inserts but then it's much easier to write queries later. What do Persons and Trusts have in common? Are they both registrants of some system? If so you could create 1 address table. If not then maybe 2 address tables?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Hi ratbak,
Yes, we need to track if a plot is subdivided or merged. Can you explain how to do this?
Thanks
-
Yes, we need to track if a plot is subdivided or merged. Can you explain how to do this?
Maybe an ParentPlot/AncestorPlot table association table, with PlotID, ParentPlotID, ChangeType (SubDivide, Merge), DateChanged, &any other attributes you need to track. This would would allow for many-to-many between parent and child plots.
-
Sorry, I am not sure how to model this. Are you able to show an example with a diagram, please?
Thanks a lot.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply