From Koha Wiki

Contents 1 Koha DB schema inconsistencies 1.1 Table names 1.2 Column names 1.3 Column to change 1.4 Missing foreign keys 1.5 No longer used tables and table columns 1.6 resume from kohastructure.sql

Koha DB schema inconsistencies

The following page tries to reference all strange things in the Koha database. They'll have to be fixed in a future version, or they are strange but assumed and are in Koha for a good reason.

Short reading = the topics that are debatable are in italic. Unless we (joubu & paul_p) made a mistake when investigating all other lines are wrong database design and should be fixed. If you have time, you're welcomed reading everything and adding your thoughts. We may have missed some things, so you're also welcomed to add whatever you think it's worth adding

Table names

• Table names are sometimes singular (biblio), sometimes plural (items). Rough numbers: 85 are plural, 50 are singular
• Table name sometimes include a _ (marc_tag_structure), sometime they don't (biblioitems). 70 have a _ and 64 dont (some of them don't need one anyway, like items or borrowers)
• A few tables are prefixed with aq (acquisition), others aren't
• opac_news table is not only for opac news, but also for staff interface. Should have another name
• should we call 'users of the libraries' members, patrons, borrowers or users ? Choose one term & stick with it. At the moment we use different terms in different places: borrowers as table name C4/Members.pm as package, patron on interface, user for user_permission
• z3950servers table should be rename in "available_search_servers"
• Some tables don't have any primary key (maybe for some of them it's OK):

accountlines			=> Done/Stéphane Delaune bugz:7671/Need Signoff
* No PK 

accountoffsets			=> 
* No PK

aqorderdelivery			=>
* No PK

borrower_attributes		=>
* No PK

branches			=>
* No PK. (possible = branchcode)

branchtransfers
* No PK

browser				=>
* No PK

deletedborrowers		=>
* No PK

fundmapping			=> does not exist in kohastructure
* No PK
	
import_biblios			=>
* No PK

import_record_matches		=>
* No PK

language_script_bidi		=>
* No PK

language_script_mapping		=>
* No PK

matcher_matchpoints		=>
* No PK

matchpoint_component_norms	=>
* No PK

message_queue			=>
* No PK. (possible = message_id)

notifys				=>
* No PK. (we have a field named notify_id but it's a normal field, no unique and no autoincrement)

nozebra				=> this table is deprecated, it's OK
* No PK

old_issues			=>
* No PK

old_reserves			=>
* No PK

reserveconstraints		=>
* No PK

reserves			=>
* No PK

search_history			=>
* No PK

serialitems			=>
* No PK. (idea: delete this table and create either in items.serialid or in serial.itemnumber)

sessions			=> this table has a UNIQUE KEY `id`, we should move to PRIMARY KEY
* No PK (possible = id)

statistics			=>
* No PK

stopwords			=>
* No PK

tmp_holdsqueue			=>
* No PK

user_permissions		=>
* No PK

virtualshelfcontents		=> 
* No PK

• the table need_merge_authorities could be removed and replaced by a column in authority table
• the tables biblio and biblioitems should be merged (question : is it a good idea, they could be usefull for FRBR in the future...)
• the word "category" is used in many places (shelveds, borrower_attribute_type, branchcategories,...) and we have a table categories related to borrower category. We should rename this table to borrower_category
• the tables default_borrower_circ_rules, default_branch_circ_rules, default_branch_item_rules, default_circ_rules are not always related to what their name says
  • default_borrower_circ_rule => related to borrower category code (should be named default_borrower_category_circ_rule ? there is a 1-1 relation with categories, so this table should be completely removed in favor of a new column in categories)
  • default_branch_circ_rules => related to branch circ rules. There is a 1-1 relation with branches, so this table should be completly removed in favor of 2 new column in branches)
  • default_branch_item_rules => related to itemtype rules. At the very least, this table should be renamed to default_item_circ_rules. In fact, the table should be completly removed in favor of a new column in branches table
  • default_circ_rules => this table is completly useless, as it just can contain 1 line (the primary key is a column that can only have "singleton" value !). Should be removed in favor of a systempreference
• the tables language_ are constants, they are just filled at the setup of Koha. We should remove them in favor of a YAML or any kind of static file.
• message_transport_types contains only 1 column, that is a FK for message_transports. We should remove it in favor of SELECT DISTINCT message_transport_type in message_transport (that's what we already do with authorised_values.category)
• the table patroncard seem to be unused and could be removed
• the table subscriptionhistory should be merged with subscription, as there is a 1-1 relation between them

Strange things that are are not bugs or inconsistencies:

• Some tables have a old_ prefix, some have a deleted_ prefix. The old_ prefix means the information is kept here and used in Koha. For example, issues once the item has been returned are moved to old_issues table. The deleted_ prefix means we store here a record that has been deleted, and is not used anywhere by Koha.

Column names

• primary keys column name can be: id (in authorised_values table), xxxid (cityid), xxx_id (label_id), xxxId (limitId), xxxcode (branchcode), xxxnumber (borrowernumber) => choose how to name PK and update schema accordingly
• Column names sometimes have a _ (biblioitems.cn_source), sometimes they don't (items.notforloan)
• Column names are usually lowercase, some have a capital (exhaustive list = deletedborrowers.B_*, borrowers.B_* fields, old_reserves.lowestPriority, reserves.lowestPriority, suggestions.STATUS) => we should switch the Uppercase letters to lowercase
• Some column names are prefixed with almost the table name (aqbudgetperiod.budget_period_*, aqcontract.contract*, aqbudgets.budget*, messages.message_*) => we should remove those prefixes

Column to change

• aqbudgetperiods.sort1_authcat (and sort2_authcat) is unused & should be removed
• aqorders.sort1_authcat (and sort2_authcat) is unused and should be removed

(aqbudgets.sort1_authcat is OK. It contains the authorised_values category to display to the user, that is used to fill aqorders.sort1)

• borrowers.sex is a varchar(1) and should be ENUM ('M','F') NULL DEFAULT NULL
• most (all ?) tinyint(1) fields are used as boolean and could be switched to boolean type
• collections.colBranchcode is varchar(4) => should be varchar(10) with a foreign key on branches.branchcode
• debatable= letters table has a PK based on 2 fields. It could be switched to a id (for example: overduerules contains letter1, letter2, and letter3, that is only a part of the primary key. We know that overduerules are related to 'CIRC' but it's not obvious in the database => poor design [the author of this comment is also the author of this design ;-) ] )
• reviews.borrowernumber and reviews.biblionumber should be NOT NULL (instead of DEFAULT NULL atm)
• saved_reports.report_id => isn't this the saved_sql.id ? if yes, then we should:
  • rename the column to saved_reports.saved_sql_id
  • add a FK (ON DELETE CASCADE)
• serial.biblionumber must be changed to INT(11) (instead of varchar(100) at the moment)
• serial.subscriptionid must be changed to INT(11) (instead of varchar(100) at the moment)
• search_history.userid should be renamed borrowernumber
• auth_subfield_structure has a frameworkcode field that is unused and can be removed
• matchpoint_component_norms.sequence and matchpoint_components.sequence should be renamed to avoid name collisions
• opac_news.new should be renamed to avoid name collisions
• patronimage has a constraint against a nullable field (borrower.cardnumber) - surely this should be against borrowernumber (and/or cardnumber in patronimage should not allow null)
• z3950servers.db sould be renamed dbname
• z3950servers.name sould be renamed label

Missing foreign keys

• accountlines.accountno should be a foreign key for accountoffsets.accountno (note PP: to be confirmed, i'm not sure)

• borrowers.borrowernumber should be a foreign key for
  • alert.borrowernumber (with ON DELETE CASCADE)
  • statistics.borrowernumber (with ON DELETE SET NULL)
  • messages.borrowernumber (with ON DELETE SET NULL)
  • reserveconstraints.borrowernumber (with ON DELETE CASCADE)
  • reviews.borrowernumber (with ON DELETE CASCADE)
  • saved_sql.borrowernumber (with ON DELETE SET NULL)
  • search_history.borrowernumber (with ON DELETE CASCADE) -the borrowernumber field is called userid atm, see below-
  • suggestions.suggestedby, suggestion.managedby, acceptedby, rejectedby (with ON DELETE SET NULL)

• aqbudgetperiods.budget_period_id should be a FK for:
  • aqbudgets_planning.budget_period_id (ON DELETE CASCADE)

• biblio.biblionumber should be a FK for:
  • reserveconstraints.biblionumber (with ON DELETE CASCADE)
  • reviews.biblionumber (with ON DELETE CASCADE)
  • serial.biblionumber (with ON DELETE CASCADE)
  • suggestions.biblionumber (with ON DELETE SET NULL) --note the biblionumber is NULL at the beginning of the suggestion process, so we must check the FK will work well with biblionumber=NULL

• biblio_framework.frameworkcode should be a FK for:
  • biblio.frameworkcode (with ON DELETE SET NULL)
  • marc_tag_structure.frameworkcode (with ON DELETE CASCADE)
  • marc_subfield_structure.frameworkcode (with ON DELETE CASCADE)
  • fieldmapping.frameworkcode (with ON DELETE CASCADE)

• branches.branchcode should be a FK for
  • branch_transfer_limits.toBranch and fromBranch (ON DELETE CASCADE)
  • deletedborrowers.branchcode (ON DELETE SET NULL)
  • import_batches.branchcode (ON DELETE CASCADE)
  • import_records.branchcode (ON DELETE CASCADE)
  • import_items.branchcode (ON DELETE CASCADE)
  • issues.branchcode (ON DELETE CASCADE)
  • issuingrules.branchcode (ON DELETE CASCADE)
  • old_issues.branchcode (ON DELETE SET NULL)
  • old_reserves.branchcode (ON DELETE CASCADE)
  • overduerules.branchcode (ON DELETE CASCADE)
  • repeatable_holidays.branchcode (ON DELETE CASCADE
  • special_holidays.branchcode (ON DELETE CASCADE)
  • item_circulation_alert_preferences.branchcode (ON DELETE CASCADE)
  • messages.branchcode (ON DELETE SET NULL)

• categories.categorycode should be a FK for
  • item_circulation_alert_preferences.categorycode (ON DELETE CASCADE)
  • overduerules.categorycode (ON DELETE CASCADE)

• itemtypes.itemtype should be a FK for
  • items.itype (ON DELETE SET NULL)

• subscription.subscriptionid should be a FK for
  • serial.subscriptionid

• sessions.sessionid should be a FK for
  • search_history.sessionid (ON DELETE SET NULL) -- it's used to distinguish current session & previous search history

Strange things thare are not bugs or inconsistencies:

• could/should cities table be a foreign key of borrowers.city ? no, because it's just a pre-filled list of common cities for this library. The borrower city can also be entered manually.
• suggestions.branchcode and subscription.branchcode don't have a FK to branches table because they can (or are) be empty sometimes, breaking the FK. Question: would it be possible/working to have this field NULL and a foreign key placed ? to be tested...
• aqorders.branchcode should have a FK on branches if the answer to the previous question is positive

No longer used tables and table columns

Probably unused tables:

• ethnicity
  
• notifys
  
• printers
  

Probably unused columns:

• borrowers.ethnotes / deletedborrowers.ethnotes
• borrowers.ethnicity / deletedborrowers.ethnicity

resume from kohastructure.sql

Table names	First primary key	Second primary key	Third primary key	possible existing unique key
accountlines	accountlinesid
accountoffsets
action_logs	action_id
alert	alertid
aqbasket	basketno
aqbasketgroups	id
aqbooksellers	id
aqbudgetperiods	budget_period_id
aqbudgets	budget_id
aqbudgets_planning	plan_id
aqcontract	contractnumber
aqorderdelivery
aqorders	ordernumber
aqorders_items	itemnumber
auth_header	authid
auth_subfield_structure	authtypecode	tagfield	tagsubfield
auth_tag_structure	authtypecode	tagfield
auth_types	authtypecode
authorised_values	id
biblio	biblionumber
biblio_framework	frameworkcode
bibliocoverimage	imagenumber
biblioitems	biblioitemnumber
borrower_attribute_types	code
borrower_attributes
borrower_message_preferences	borrower_message_preference_id
borrower_message_transport_preferences	borrower_message_preference_id	message_transport_type
borrowers	borrowernumber
branch_borrower_circ_rules	categorycode	branchcode
branch_item_rules	itemtype	branchcode
branch_transfer_limits	limitId
branchcategories	categorycode
branches				branchcode
branchrelations	branchcode	categorycode
branchtransfers
browser
categories	categorycode
cities	cityid
class_sort_rules	class_sort_rule
class_sources	cn_source
collections	colId
collections_tracking	ctId
creator_batches	label_id
creator_images	image_id
creator_layouts	layout_id
creator_templates	template_id
currency	currency
default_borrower_circ_rules	categorycode
default_branch_circ_rules	branchcode
default_branch_item_rules	itemtype
default_circ_rules	singleton
deletedbiblio	biblionumber
deletedbiblioitems	biblioitemnumber
deletedborrowers
deleteditems	itemnumber
ethnicity	code
export_format	export_format_id
fieldmapping	id
hold_fill_targets	itemnumber
import_batches	import_batch_id
import_biblios
import_items	import_items_id
import_record_matches
import_records	import_record_id
issues
issuingrules	branchcode	categorycode	itemtype
item_circulation_alert_preferences	id
items	itemnumber
itemtypes	itemtype
language_descriptions	id
language_rfc4646_to_iso639	id
language_script_bidi
language_script_mapping
language_subtag_registry	id
letter	module	code
marc_matchers	matcher_id
marc_subfield_structure	frameworkcode	tagfield	tagsubfield
marc_tag_structure	frameworkcode	tagfield
matchchecks	matchcheck_id
matcher_matchpoints
matchpoint_component_norms
matchpoint_components	matchpoint_component_id
matchpoints	matchpoint_id
message_attributes	message_attribute_id
message_queue				message_id
message_transport_types	message_transport_type
message_transports	message_attribute_id	message_transport_type	is_digest
messages	message_id
need_merge_authorities	id
notifys
nozebra
old_issues
old_reserves
opac_news	idnew
overduerules	branchcode	categorycode
patroncards	cardid
patronimage	cardnumber
pending_offline_operations	operationid
permissions	module_bit	code
printers	printername
printers_profile	profile_id
repeatable_holidays	id
reports_dictionary	id
reserveconstraints
reserves
reviews	reviewid
roadtype	roadtypeid
saved_reports	id
saved_sql	id
search_history
serial	serialid
serialitems
services_throttle	service_type
sessions				id
special_holidays	id
statistics
stopwords
subscription	subscriptionid
subscriptionhistory	subscriptionid
subscriptionroutinglist	routingid
suggestions	suggestionid
systempreferences	variable
tags	entry
tags_all	tag_id
tags_approval	term
tags_index	term	biblionumber
tmp_holdsqueue
user_permissions
userflags	bit
virtualshelfcontents
virtualshelves	shelfnumber
z3950servers	id
zebraqueue	id