dimanche 19 juin 2016

Design SQL Insert When Using Modular Address

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