Bug 17230 - borrowers table should be better normalized
Summary: borrowers table should be better normalized
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Galen Charlton
QA Contact: Testopia
URL:
Keywords:
: 18218 (view as bug list)
Depends on:
Blocks:
 
Reported: 2016-08-31 14:14 UTC by Barton Chittenden
Modified: 2020-01-11 23:20 UTC (History)
1 user (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Barton Chittenden 2016-08-31 14:14:43 UTC
I've always been bothered by the fact that we have 3 separate sets of contact information in the borrowers table:

streetnumber    
streettype                
address    
address2    
city    
state    
zipcode    
country    
email    
phone    

B_streetnumber
B_streettype
B_address
B_address2
B_city
B_state
B_zipcode
B_country
B_email
B_phone

altstreetnumber
altstreettype
altaddress
altaddress2
altcity
altstate
altzipcode
altcountry
altemail
altphone

These should be moved to a contacts table, which contains a 'contact_type' field corresponding to 'primary', 'B_' or 'alt' (although these also desperately need to be renamed), as well as a link to the borrower and a timestamp.
Comment 1 Fridolin Somers 2017-07-28 12:21:47 UTC
Nice idea, more relational database indeed
Comment 2 Barton Chittenden 2018-04-26 00:34:30 UTC
(In reply to Barton Chittenden from comment #0)
> I've always been bothered by the fact that we have 3 separate sets of
> contact information in the borrowers table:
> 
> streetnumber    
> streettype                
> address    
> address2    
> city    
> state    
> zipcode    
> country    
> email    
> phone    
> 
> B_streetnumber
> B_streettype
> B_address
> B_address2
> B_city
> B_state
> B_zipcode
> B_country
> B_email
> B_phone
> 
> altstreetnumber
> altstreettype
> altaddress
> altaddress2
> altcity
> altstate
> altzipcode
> altcountry
> altemail
> altphone
> 
> These should be moved to a contacts table, which contains a 'contact_type'
> field corresponding to 'primary', 'B_' or 'alt' (although these also
> desperately need to be renamed), as well as a link to the borrower and a
> timestamp.

alt* is actually altcontact*.

Other borrower contact fields:

mobile
fax
emailpro
phonepro
smsalertnumber
sms_provider_id

Contact fields from other tables:

aqbooksellers:

address1
address2
address3
address4
phone
booksellerfax
bookselleremail
booksellerurl
postal
url
fax

branches:

branchaddress1
branchaddress2
branchaddress3
branchzip
branchcity
branchstate
branchcountry
branchphone
branchfax
branchemail
branchreplyto
branchreturnpath
branchurl 
geolocation (?)

This will require us to use a contact_id and some linking tables (borrower_contact_link, branch_contact_link, aqbooksellers_contact_link)
Comment 3 Katrin Fischer 2020-01-11 23:20:02 UTC
*** Bug 18218 has been marked as a duplicate of this bug. ***
Comment 4 Katrin Fischer 2020-01-11 23:20:36 UTC
Bug 18218 suggested to remodel the aqcontacts table for this.