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)

can you post the existing table structures? And some details about how you want to query this data? — Taryn
– Taryn, Commented Nov 1, 2012 at 15:28
Providing your actual schema would help. Outside of that, can you better describe some specific deficiencies in your current schema? I can't glean those from your question. — D'Arcy Rittich
– D'Arcy Rittich, Commented Nov 1, 2012 at 15:30

PRNDL Development Studios · Accepted Answer · 2012-11-01 15:40:02Z

4

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.

edited Nov 1, 2012 at 15:40

answered Nov 1, 2012 at 15:29

PRNDL Development Studios

3,3102 gold badges37 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user371051 Over a year ago

CREATE TABLE alabama ( ID int(11) NOT NULL AUTO_INCREMENT, Name varchar(255) DEFAULT NULL, State char(2) DEFAULT NULL, Parent int(11) DEFAULT NULL, Description blob, DisplayPrecedence int(11) DEFAULT NULL, PRIMARY KEY (ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 AUTO_INCREMENT=3351 ;

user371051 Over a year ago

Sorry hit enter... still gathering some info for the

PRNDL Development Studios Over a year ago

^ That is not what you want. You do not need a new table for every state.

user371051 Over a year ago

ok, I also edited/added a better example up in my post. so... still keep the relational just put all states chapters/sub-chapters together and all states end-records together and one relational table between the two correct ?

PRNDL Development Studios Over a year ago

What I wrote still stands. It will bring you to 4 tables instead of ~150. You will have to create 4 new tables and reorganize your data, but it will be WAY better.

|

Collectives™ on Stack Overflow

Mysql DB Is this the most efficient design?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related