We don't have context here so it is hard to advise. If you were to look at the tax records in the town hall, you would see this as ONE record. But, that is because the record book is a "flat file". With a relational database, you can make this two tables (or the 6 I suggest). That allows you to easily find all the properties purchased by a single buyer. It also allows you to have joint owners for a particular property. And then there is the history if you need to see purchases and sales over time. In the town all, the last purchase/sale may not even be in the same book as the current one because items are entered as they are recorded.
1. Do not prefix your field names. You will find this very annoying once you start trying to use the column names and have to type some number of useless characters before you can get any meaningful intellisense. There are cases where a suffix might be useful to distinguish between things like shipping and billing addresses. Addr1_shp, Addr2_shp, City_shp,.... Addr1_bil, Addr2_bil, City_bil, ....
2. All PK's should have meaningful names. don't use ID for all the PK names.
3. For sanity sake, be consistent in how you name things and follow some set of standards. the sufix "ID" is generally reserved for autonumbers. So AgtID would be the autonumber PK of the Agent table.
4. Buyers, sellers, agents are all "persons". Therefore they can all be stored in the same table. In fact, buyers are sellers at a different point in time and agents can be either a buyer or a seller and an agent in the same transaction.
5. You don't show all the columns of your Buyer table but I'm guessing it has lots of duplication of columns because you have at a minimum slots for buyer, seller, and agent. and possibly two of each.
So as tables I see:
1. Persons - name and address and other info that occurs only once.
2. ContactNumbers - allows multiple phone, email, etc ways to contact any person. If you put these in the person record, you keep having to change the format of the table as we get new ways to ID a person. Here is their X name and here is their FB name and this is their office phone and their cell phone and their home phone and emails to match, etc.
3, Roles - Buyer, Seller, Agent, etc?
4. Property - property description, block, lot, etc.
5. Transaction - PropertyID, date, time ( there could be back to back closings so the property might change hands multiple times in the same day), purchasePrice, etc.
6. TransactionPersons - this is the junction table that ties it all together. This junction table has a 3-part PK rather than the normal 2-part PK. TransID, PersonID, RoleID - at least two rows required for a complete transaction. One for buyer. One for Seller. This will be a complicated business rule to enforce. Then you can have multiple other parties. So, multiple sellers, multiple buyers, even multiple agents.
Once you define all your tables, don't forget to create relationships.
Screenshot (58).png
