In designing my database, I created the addresses as a modular system. One table has the AddressID
, and the rest of the address tables have a row for both the AddressID
and their type of information, (i.e. Phone Number, Email Address, Physical Address, etc.). This is to save space and add extensibility for addresses that might not have information for every column and future addresses that might have more information than I anticipate.
Other entities in the database have a column for AddressID
, (tables like InsuranceCompany
and BranchOffice
, etc.). When I'm inserting a new row in one of these entities, I need to supply the AddressID
. I'm wondering, what is the best way of designing this? Can I have a view with all of the various information types, (Phone Number, Physical Address, etc.) as columns and then do an insert into the view first, and take an output of the AddressID
for an insert into the InsuranceOffice
entity? Should I use a stored procedure?
Table 1 - Address
AddressID int identity
Table 2 - PhoneNumber
AddressID int
PhoneNumber varchar(10)
Table 3 - PhoneNumberForeignPart
AddressID int
CountryCode varchar(5)
PhoneNumberSuffix varchar(5)
Table 4 - PhoneNumberExtenstion
AddressID int
PhoneNumberExtension varchar(5)
Table 5 - EmailAddress
AddressID int
EmailAddress varchar(50)
...
Table 10 - InsuranceCompany
InsuranceCompanyID int identity
InsuranceCompanyName varchar(40)
AddressID int
Disabled bit
Thanks.
Aucun commentaire:
Enregistrer un commentaire