Mysql DB Is this the most efficient design?

Question

I have an existing mysql DB that manages regulations for 50 states. The current setup is relational - three tables for EACH of the 50 states:

state_table contains the chapter/sub-chapter headings item_table contains the end records department_table contains the ID's to relate the two.

all combined it handles around 620,000 records

I'm not a DB design expert and have always utilized this as-is and gotten-by however, the nature of tables for all 50 states limits searching across all states etc. and I'm wondering if there is a better approach.

I'm wondering if I should consider combining this into either a single set of 3 relational tables for the entire nation or even a single table to handle everything.

I've asked this on other forums and have been told to read various volumes of DB schema and structures etc. so if there is someone who can just suggest the direction to go in and the pro's and con's of what I have vs the alternative that would be great!

thanks!

Here's the way it is, X 50

alabama

ID
Name
State
Parent Description

alabama_department

Department - ID's from "alabama"

Item - ID's from "alabama_item"

alabama_item

ID
Name
Description
Keywords
Doc_ID
Effective_date
... ...

The Queries: I step through the heirarchy of chapter/sub-chapter/end-record via links this works fine but I'm starting to focus more on search capability and also thinking what I have is overkill and it sounds like a couple of you think so (overkill)

Answer 1

If I am correct in thinking you have 150 tables (3 * 50 states) Then:

You should have a 'states' table which includes a stateID and stateName. Then use ONE table for chapter/subchapters, ONE for departments, and ONE for end records and use the stateID to relate different records to a state.

You should not have 3 tables for each state, you can use one of each and just relate to a state table. This brings you to four tables instead of 150.