Database design question
-
Hi, thanks for looking Im devoloping a system (to buy and sell houses), and im having some isues with my current DB design and .net datasets, so i wonder how you guys make it, any advices will be appreciated, thanks The table is.....Addresses The thing is that many objects in y system can have an address, for example: Customer House Notary Employee Office And some others So i decided to put the AddressID in the table corresponding to the object Table Customers CustomerID ... AddressID Table Houses HouseID ... AddressID Table X XID ... AddressID The thing is that im using .net datasets with foreing keys, wich wont let me add a house row if i havent created the address row, so i have to add automatically an address row before the house row, this has some isues, like i have to set all nonnullable values in address row to something before adding this row. It wouldnt make any sense to ask for the adress before the customer or house general info. So im just wondering, what would be some other approach Aditional Info: Each object can only have one address 1 customer = 1 address 1 house = 1 address ... Thanks in advance for any feedback
Alexei Rodriguez
-
Hi, thanks for looking Im devoloping a system (to buy and sell houses), and im having some isues with my current DB design and .net datasets, so i wonder how you guys make it, any advices will be appreciated, thanks The table is.....Addresses The thing is that many objects in y system can have an address, for example: Customer House Notary Employee Office And some others So i decided to put the AddressID in the table corresponding to the object Table Customers CustomerID ... AddressID Table Houses HouseID ... AddressID Table X XID ... AddressID The thing is that im using .net datasets with foreing keys, wich wont let me add a house row if i havent created the address row, so i have to add automatically an address row before the house row, this has some isues, like i have to set all nonnullable values in address row to something before adding this row. It wouldnt make any sense to ask for the adress before the customer or house general info. So im just wondering, what would be some other approach Aditional Info: Each object can only have one address 1 customer = 1 address 1 house = 1 address ... Thanks in advance for any feedback
Alexei Rodriguez
Your initial design motivation was a good one, but is it really necessary to extract the House Address in your database to a separate table? (Same is true for Customer) The idea behind making a foreign key reference is to eliminate storing duplicate data. In your situation, if you store the House and Customer Address in their respective tables, you wouldn't be storing duplicate data. IMHO, This sounds like an "overdesign" of your DB schema. Oops. :doh:
-
Your initial design motivation was a good one, but is it really necessary to extract the House Address in your database to a separate table? (Same is true for Customer) The idea behind making a foreign key reference is to eliminate storing duplicate data. In your situation, if you store the House and Customer Address in their respective tables, you wouldn't be storing duplicate data. IMHO, This sounds like an "overdesign" of your DB schema. Oops. :doh:
I didn't get that from his explanation, I got that there is 1 address table and a FK to all the object table. This is a correct design and I would stick with it. I would however change the FK constraint so there can be a customer without an address etc.
Never underestimate the power of human stupidity RAH
-
Your initial design motivation was a good one, but is it really necessary to extract the House Address in your database to a separate table? (Same is true for Customer) The idea behind making a foreign key reference is to eliminate storing duplicate data. In your situation, if you store the House and Customer Address in their respective tables, you wouldn't be storing duplicate data. IMHO, This sounds like an "overdesign" of your DB schema. Oops. :doh:
Thanks David for the suggestion Ill add the address info in the same table for some objects And use the separate address table for others like customers, because one never knows... what if later they decide to add more than one address to a customer, employee...
Alexei Rodriguez
-
Thanks David for the suggestion Ill add the address info in the same table for some objects And use the separate address table for others like customers, because one never knows... what if later they decide to add more than one address to a customer, employee...
Alexei Rodriguez
:)
-
I didn't get that from his explanation, I got that there is 1 address table and a FK to all the object table. This is a correct design and I would stick with it. I would however change the FK constraint so there can be a customer without an address etc.
Never underestimate the power of human stupidity RAH
Hi Mycroft, glad to hear that the design is not that bad :) I decided to go with this approach, because i didnt want to repeat columns (street, number, city...) in the tables for customers, houses, employees... so all of them have an AddressID column wich is a foreing key to the Address table Thanks
Alexei Rodriguez
-
Hi Mycroft, glad to hear that the design is not that bad :) I decided to go with this approach, because i didnt want to repeat columns (street, number, city...) in the tables for customers, houses, employees... so all of them have an AddressID column wich is a foreing key to the Address table Thanks
Alexei Rodriguez
You're design is correct and good, I think your problem may be that the FK forces a constraint that an address record is required, relax this constraint and you are then able to have a cunstomer without an address record and everything works out nicely!
Never underestimate the power of human stupidity RAH