Library System — Full ILS

Entity Relationship Diagram

Foreign Key Relationships

Constraint	Table	Column	References	Referenced Column
`library_checkout_ibfk_1`	`library_checkout`	copy_id	`library_copy`	id
`library_checkout_ibfk_2`	`library_checkout`	patron_id	`library_patron`	id
`library_copy_ibfk_1`	`library_copy`	library_item_id	`library_item`	id
`library_entity_subject_map_ibfk_1`	`library_entity_subject_map`	subject_authority_id	`library_subject_authority`	id
`library_fine_ibfk_2`	`library_fine`	checkout_id	`library_checkout`	id
`library_fine_ibfk_1`	`library_fine`	patron_id	`library_patron`	id
`library_hold_ibfk_1`	`library_hold`	library_item_id	`library_item`	id
`library_hold_ibfk_2`	`library_hold`	patron_id	`library_patron`	id
`library_item_creator_ibfk_1`	`library_item_creator`	library_item_id	`library_item`	id
`fk_item_subject_authority`	`library_item_subject`	authority_id	`library_subject_authority`	id
`library_item_subject_ibfk_1`	`library_item_subject`	library_item_id	`library_item`	id
`library_order_line_ibfk_1`	`library_order_line`	order_id	`library_order`	id
`library_serial_issue_ibfk_2`	`library_serial_issue`	library_item_id	`library_item`	id
`library_serial_issue_ibfk_1`	`library_serial_issue`	subscription_id	`library_subscription`	id
`library_subscription_ibfk_1`	`library_subscription`	library_item_id	`library_item`	id

`library_item`

81 columns 12 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
	`information_object_id`	int unsigned	NOT NULL	-
	`material_type`	varchar(50)	NOT NULL	`monograph`		monograph, serial, volume, issue, chapter, article, manuscript, map, pamphlet
	`subtitle`	varchar(500)	NULL	-
	`responsibility_statement`	varchar(500)	NULL	-
	`call_number`	varchar(100)	NULL	-
	`classification_scheme`	varchar(50)	NULL	-		dewey, lcc, udc, bliss, colon, custom
	`classification_number`	varchar(100)	NULL	-
	`dewey_decimal`	varchar(50)	NULL	-
	`cutter_number`	varchar(50)	NULL	-
	`shelf_location`	varchar(100)	NULL	-
	`copy_number`	varchar(20)	NULL	-
	`volume_designation`	varchar(100)	NULL	-
	`isbn`	varchar(17)	NULL	-
	`issn`	varchar(9)	NULL	-
	`lccn`	varchar(50)	NULL	-
	`oclc_number`	varchar(50)	NULL	-
	`openlibrary_id`	varchar(50)	NULL	-
	`goodreads_id`	varchar(50)	NULL	-
	`librarything_id`	varchar(50)	NULL	-
	`openlibrary_url`	varchar(500)	NULL	-
	`ebook_preview_url`	varchar(500)	NULL	-
	`cover_url`	varchar(500)	NULL	-
	`cover_url_original`	varchar(500)	NULL	-
	`doi`	varchar(255)	NULL	-
	`barcode`	varchar(50)	NULL	-
	`edition`	varchar(255)	NULL	-
	`edition_statement`	varchar(500)	NULL	-
	`publisher`	varchar(255)	NULL	-
	`publication_place`	varchar(255)	NULL	-
	`publication_date`	varchar(100)	NULL	-
	`copyright_date`	varchar(50)	NULL	-
	`printing`	varchar(100)	NULL	-
	`pagination`	varchar(100)	NULL	-
	`dimensions`	varchar(100)	NULL	-
	`physical_details`	text	NULL	-
	`language`	varchar(100)	NULL	-
	`accompanying_material`	text	NULL	-
	`series_title`	varchar(500)	NULL	-
	`series_number`	varchar(50)	NULL	-
	`series_issn`	varchar(9)	NULL	-
	`subseries_title`	varchar(500)	NULL	-
	`general_note`	text	NULL	-
	`bibliography_note`	text	NULL	-
	`contents_note`	text	NULL	-
	`summary`	text	NULL	-
	`target_audience`	text	NULL	-
	`system_requirements`	text	NULL	-
	`binding_note`	text	NULL	-
	`frequency`	varchar(50)	NULL	-
	`former_frequency`	varchar(100)	NULL	-
	`numbering_peculiarities`	varchar(255)	NULL	-
	`publication_start_date`	date	NULL	-
	`publication_end_date`	date	NULL	-
	`publication_status`	varchar(20)	NULL	-		current, ceased, suspended
	`total_copies`	smallint unsigned	NOT NULL	`1`
	`available_copies`	smallint unsigned	NOT NULL	`1`
	`circulation_status`	varchar(30)	NOT NULL	`available`		available, on_loan, processing, lost, withdrawn, reference
	`cataloging_source`	varchar(100)	NULL	-
	`cataloging_rules`	varchar(20)	NULL	-		aacr2, rda, isbd
	`encoding_level`	varchar(20)	NULL	-
	`created_at`	timestamp	NULL	-
	`updated_at`	timestamp	NULL	-
	`heritage_asset_id`	int unsigned	NULL	-		FK to heritage_asset
	`acquisition_method`	varchar(50)	NULL	-		purchase, donation, gift, bequest, exchange, deposit
	`acquisition_date`	date	NULL	-
	`acquisition_cost`	decimal(15,2)	NULL	-
	`acquisition_currency`	varchar(3)	NULL	`ZAR`
	`replacement_value`	decimal(15,2)	NULL	-
	`insurance_value`	decimal(15,2)	NULL	-
	`insurance_policy`	varchar(100)	NULL	-
	`insurance_expiry`	date	NULL	-
	`asset_class_code`	varchar(20)	NULL	-		heritage_asset_class.code
	`recognition_status`	varchar(30)	NULL	`pending`
	`valuation_date`	date	NULL	-
	`valuation_method`	varchar(50)	NULL	-
	`valuation_notes`	text	NULL	-
	`donor_name`	varchar(255)	NULL	-
	`donor_restrictions`	text	NULL	-
	`condition_grade`	varchar(30)	NULL	-
	`conservation_priority`	varchar(20)	NULL	-

`library_item_creator`

7 columns 30 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`library_item_id`	bigint unsigned	NOT NULL	-
FK	`name`	varchar(500)	NOT NULL	-
	`role`	varchar(50)	NULL	`author`
	`sort_order`	int	NULL	`0`
	`authority_uri`	varchar(500)	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`library_item_subject`

12 columns 94 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`library_item_id`	bigint unsigned	NOT NULL	-
FK	`heading`	varchar(500)	NOT NULL	-
	`subject_type`	varchar(50)	NULL	`topic`
	`source`	varchar(100)	NULL	-
	`uri`	varchar(500)	NULL	-
	`lcsh_id`	varchar(100)	NULL	-
FK	`authority_id`	bigint unsigned	NULL	-
	`dewey_number`	varchar(50)	NULL	-
	`lcc_number`	varchar(50)	NULL	-
	`subdivisions`	json	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`library_copy`

20 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`library_item_id`	bigint unsigned	NOT NULL	-
	`copy_number`	smallint unsigned	NOT NULL	`1`
UQ	`barcode`	varchar(50)	NULL	-
FK	`accession_number`	varchar(50)	NULL	-
	`call_number_suffix`	varchar(20)	NULL	-		e.g. c.2, v.3
	`shelf_location`	varchar(100)	NULL	-
FK	`branch`	varchar(100)	NULL	-		Library branch/location
FK	`status`	varchar(30)	NOT NULL	`available`
	`condition_grade`	varchar(30)	NULL	-
	`condition_notes`	text	NULL	-
	`acquisition_method`	varchar(50)	NULL	-
	`acquisition_date`	date	NULL	-
	`acquisition_cost`	decimal(15,2)	NULL	-
	`acquisition_source`	varchar(255)	NULL	-		vendor or donor
	`withdrawal_date`	date	NULL	-
	`withdrawal_reason`	text	NULL	-
	`notes`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_patron`

30 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`actor_id`	int unsigned	NULL	-		FK to actor table
UQ	`card_number`	varchar(50)	NOT NULL	-
FK	`patron_type`	varchar(30)	NOT NULL	`public`
	`first_name`	varchar(100)	NOT NULL	-
FK	`last_name`	varchar(100)	NOT NULL	-
FK	`email`	varchar(255)	NULL	-
	`phone`	varchar(50)	NULL	-
	`address`	text	NULL	-
	`institution`	varchar(255)	NULL	-
	`department`	varchar(100)	NULL	-
	`id_number`	varchar(50)	NULL	-		National ID or student number
	`date_of_birth`	date	NULL	-
	`membership_start`	date	NOT NULL	-
FK	`membership_expiry`	date	NULL	-
	`max_checkouts`	smallint unsigned	NOT NULL	`5`
	`max_renewals`	smallint unsigned	NOT NULL	`2`
	`max_holds`	smallint unsigned	NOT NULL	`3`
FK	`borrowing_status`	varchar(20)	NOT NULL	`active`
	`suspension_reason`	text	NULL	-
	`suspension_until`	date	NULL	-
	`total_fines_owed`	decimal(10,2)	NOT NULL	`0.00`
	`total_fines_paid`	decimal(10,2)	NOT NULL	`0.00`
	`total_checkouts`	int unsigned	NOT NULL	`0`
	`last_activity_date`	date	NULL	-
	`photo_url`	varchar(500)	NULL	-
	`notes`	text	NULL	-
	`created_by`	int unsigned	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_checkout`

15 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`copy_id`	bigint unsigned	NOT NULL	-
FK	`patron_id`	bigint unsigned	NOT NULL	-
FK	`checkout_date`	datetime	NOT NULL	-
FK	`due_date`	date	NOT NULL	-
	`return_date`	datetime	NULL	-
	`renewed_count`	smallint unsigned	NOT NULL	`0`
FK	`status`	varchar(30)	NOT NULL	`active`
	`checkout_notes`	text	NULL	-
	`return_notes`	text	NULL	-
	`return_condition`	varchar(30)	NULL	-
	`checked_out_by`	int unsigned	NULL	-		Staff user_id
	`checked_in_by`	int unsigned	NULL	-		Staff user_id
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_hold`

16 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`library_item_id`	bigint unsigned	NOT NULL	-
FK	`patron_id`	bigint unsigned	NOT NULL	-
	`hold_date`	datetime	NOT NULL	-
	`expiry_date`	date	NULL	-		Hold expires if not picked up
	`pickup_branch`	varchar(100)	NULL	-
	`queue_position`	smallint unsigned	NOT NULL	`1`
FK	`status`	varchar(30)	NOT NULL	`pending`
	`notification_sent`	tinyint(1)	NOT NULL	`0`
	`notification_date`	datetime	NULL	-
	`fulfilled_date`	datetime	NULL	-
	`cancelled_date`	datetime	NULL	-
	`cancel_reason`	text	NULL	-
	`notes`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_fine`

19 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`patron_id`	bigint unsigned	NOT NULL	-
FK	`checkout_id`	bigint unsigned	NULL	-
FK	`fine_type`	varchar(30)	NOT NULL	`overdue`
	`amount`	decimal(10,2)	NOT NULL	-
	`paid_amount`	decimal(10,2)	NOT NULL	`0.00`
	`currency`	varchar(3)	NOT NULL	`ZAR`
FK	`status`	varchar(20)	NOT NULL	`outstanding`
	`description`	text	NULL	-
FK	`fine_date`	date	NOT NULL	-
	`payment_date`	datetime	NULL	-
	`payment_method`	varchar(30)	NULL	-
	`payment_reference`	varchar(100)	NULL	-
	`waived_by`	int unsigned	NULL	-		Staff user_id who waived
	`waived_date`	datetime	NULL	-
	`waive_reason`	text	NULL	-
	`notes`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_loan_rule`

12 columns 11 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`material_type`	varchar(50)	NOT NULL	-
	`patron_type`	varchar(30)	NOT NULL	`*`		* = all patron types
	`loan_period_days`	smallint unsigned	NOT NULL	`14`
	`renewal_period_days`	smallint unsigned	NOT NULL	`14`
	`max_renewals`	smallint unsigned	NOT NULL	`2`
	`fine_per_day`	decimal(10,2)	NOT NULL	`1.00`
	`fine_cap`	decimal(10,2)	NULL	-		Max fine for this type
	`grace_period_days`	smallint unsigned	NOT NULL	`0`
	`is_loanable`	tinyint(1)	NOT NULL	`1`
	`notes`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`library_budget`

15 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`budget_code`	varchar(50)	NOT NULL	-
	`fund_name`	varchar(255)	NOT NULL	-
FK	`fiscal_year`	varchar(9)	NOT NULL	-		e.g. 2025/2026
	`allocated_amount`	decimal(15,2)	NOT NULL	`0.00`
	`committed_amount`	decimal(15,2)	NOT NULL	`0.00`		On order
	`spent_amount`	decimal(15,2)	NOT NULL	`0.00`		Received/invoiced
	`currency`	varchar(3)	NULL	`ZAR`
FK	`category`	varchar(50)	NULL	-		monographs, serials, electronic, etc.
	`department`	varchar(100)	NULL	-
	`notes`	text	NULL	-
FK	`status`	varchar(20)	NOT NULL	`active`
	`created_by`	int unsigned	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_order`

25 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
UQ	`order_number`	varchar(50)	NOT NULL	-
FK	`vendor_id`	int unsigned	NULL	-
	`vendor_name`	varchar(255)	NULL	-
FK	`order_date`	date	NOT NULL	-
	`expected_date`	date	NULL	-
	`received_date`	date	NULL	-
FK	`status`	varchar(30)	NOT NULL	`draft`
	`order_type`	varchar(30)	NOT NULL	`purchase`
FK	`budget_code`	varchar(50)	NULL	-
	`subtotal`	decimal(15,2)	NOT NULL	`0.00`
	`tax`	decimal(15,2)	NOT NULL	`0.00`
	`shipping`	decimal(15,2)	NOT NULL	`0.00`
	`total`	decimal(15,2)	NOT NULL	`0.00`
	`currency`	varchar(3)	NULL	`ZAR`
	`invoice_number`	varchar(100)	NULL	-
	`invoice_date`	date	NULL	-
	`payment_status`	varchar(30)	NULL	`unpaid`
	`shipping_address`	text	NULL	-
	`notes`	text	NULL	-
	`approved_by`	int unsigned	NULL	-
	`approved_date`	datetime	NULL	-
	`created_by`	int unsigned	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_order_line`

21 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`order_id`	bigint unsigned	NOT NULL	-
FK	`library_item_id`	bigint unsigned	NULL	-		Link to catalog record if exists
	`title`	varchar(500)	NOT NULL	-
FK	`isbn`	varchar(17)	NULL	-
	`issn`	varchar(9)	NULL	-
	`author`	varchar(255)	NULL	-
	`publisher`	varchar(255)	NULL	-
	`edition`	varchar(100)	NULL	-
	`material_type`	varchar(50)	NULL	-
	`quantity`	smallint unsigned	NOT NULL	`1`
	`unit_price`	decimal(15,2)	NOT NULL	`0.00`
	`discount_percent`	decimal(5,2)	NOT NULL	`0.00`
	`line_total`	decimal(15,2)	NOT NULL	`0.00`
	`quantity_received`	smallint unsigned	NOT NULL	`0`
	`received_date`	date	NULL	-
FK	`status`	varchar(30)	NOT NULL	`ordered`
	`budget_code`	varchar(50)	NULL	-
	`fund_code`	varchar(50)	NULL	-
	`notes`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`library_subscription`

19 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`library_item_id`	bigint unsigned	NOT NULL	-		Parent serial/periodical
	`vendor_id`	int unsigned	NULL	-		FK to vendor
	`subscription_number`	varchar(100)	NULL	-
FK	`status`	varchar(30)	NOT NULL	`active`
	`start_date`	date	NOT NULL	-
	`end_date`	date	NULL	-
FK	`renewal_date`	date	NULL	-
	`frequency`	varchar(30)	NULL	-		From ahg_dropdown
	`issues_per_year`	smallint unsigned	NULL	-
	`cost_per_year`	decimal(10,2)	NULL	-
	`currency`	varchar(3)	NULL	`ZAR`
	`budget_code`	varchar(50)	NULL	-
	`routing_list`	json	NULL	-		Ordered list of staff for routing
	`delivery_method`	varchar(30)	NULL	-		mail, electronic, both
	`notes`	text	NULL	-
	`created_by`	int unsigned	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_serial_issue`

20 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`subscription_id`	bigint unsigned	NOT NULL	-
FK	`library_item_id`	bigint unsigned	NOT NULL	-
FK	`volume`	varchar(20)	NULL	-
	`issue_number`	varchar(20)	NULL	-
	`part`	varchar(20)	NULL	-
	`supplement`	varchar(50)	NULL	-
	`issue_date`	date	NULL	-
FK	`expected_date`	date	NULL	-
	`received_date`	date	NULL	-
FK	`status`	varchar(30)	NOT NULL	`expected`
	`claim_date`	date	NULL	-
	`claim_count`	smallint unsigned	NOT NULL	`0`
UQ	`barcode`	varchar(50)	NULL	-
	`shelf_location`	varchar(100)	NULL	-
	`bound_volume_id`	bigint unsigned	NULL	-		FK to bound volume record
	`notes`	text	NULL	-
	`checked_in_by`	int unsigned	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_ill_request`

32 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
UQ	`request_number`	varchar(50)	NOT NULL	-
FK	`direction`	varchar(20)	NOT NULL	-		borrowing or lending
FK	`patron_id`	bigint unsigned	NULL	-		Borrowing patron
FK	`partner_library`	varchar(255)	NOT NULL	-
	`partner_contact`	varchar(255)	NULL	-
	`partner_email`	varchar(255)	NULL	-
	`title`	varchar(500)	NOT NULL	-
	`author`	varchar(255)	NULL	-
	`isbn`	varchar(17)	NULL	-
	`issn`	varchar(9)	NULL	-
	`publisher`	varchar(255)	NULL	-
	`publication_year`	varchar(10)	NULL	-
	`volume_issue`	varchar(100)	NULL	-
	`pages`	varchar(50)	NULL	-
FK	`library_item_id`	bigint unsigned	NULL	-		Our item (if lending)
	`copy_id`	bigint unsigned	NULL	-
FK	`status`	varchar(30)	NOT NULL	`requested`
FK	`request_date`	date	NOT NULL	-
	`needed_by`	date	NULL	-
	`shipped_date`	date	NULL	-
	`received_date`	date	NULL	-
	`due_date`	date	NULL	-
	`return_date`	date	NULL	-
	`shipping_method`	varchar(50)	NULL	-
	`tracking_number`	varchar(100)	NULL	-
	`cost`	decimal(10,2)	NULL	-
	`currency`	varchar(3)	NULL	`ZAR`
	`notes`	text	NULL	-
	`created_by`	int unsigned	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_settings`

7 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int unsigned	NOT NULL	-	auto_increment
UQ	`setting_key`	varchar(100)	NOT NULL	-
	`setting_value`	text	NULL	-
	`setting_type`	varchar(37)	NULL	`string`		string, integer, boolean, json
	`description`	varchar(255)	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`library_subject_authority`

16 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`heading`	varchar(500)	NOT NULL	-		The subject heading text
FK	`heading_normalized`	varchar(500)	NOT NULL	-		Normalized form for matching
FK	`heading_type`	varchar(61)	NULL	`topical`		topical, personal, corporate, geographic, genre, meeting
FK	`source`	varchar(50)	NULL	`lcsh`		Source vocabulary (lcsh, mesh, local, etc.)
	`lcsh_id`	varchar(100)	NULL	-		Authority record ID (e.g., sh85034652)
	`lcsh_uri`	varchar(500)	NULL	-		Full URI to authority record
	`suggested_dewey`	varchar(50)	NULL	-		Suggested Dewey classification for this subject
	`suggested_lcc`	varchar(50)	NULL	-		Suggested LCC classification for this subject
	`broader_terms`	json	NULL	-		Parent/broader subject terms
	`narrower_terms`	json	NULL	-		Child/narrower subject terms
	`related_terms`	json	NULL	-		Related subject terms
FK	`usage_count`	int unsigned	NULL	`1`		Number of times used in catalog
	`first_used_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`last_used_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`library_entity_subject_map`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`entity_type`	varchar(50)	NOT NULL	-		NER entity type (PERSON, ORG, GPE, etc.)
	`entity_value`	varchar(500)	NOT NULL	-		Original entity value
	`entity_normalized`	varchar(500)	NOT NULL	-		Normalized entity value for matching
FK	`subject_authority_id`	bigint unsigned	NOT NULL	-		FK to subject authority
	`co_occurrence_count`	int unsigned	NULL	`1`		Times this entity appeared with this subject
FK	`confidence`	decimal(5,4)	NULL	`1.0000`		Confidence score for the mapping
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

Notes

Log in to add notes.

Legend

PK Primary Key FK Foreign Key / Index UQ Unique Constraint

Table structures are read live from the database. Row counts reflect current data.