sql normalisation

I have 4 tables, courts, divisions, addresses and telephone_numbers.

Each court may have one or more divisions, civil, criminal, family etc. Each of these combination's may have one or more addresses i.e. the civil division for a particular court may or may not be at a different physical address to the criminal court. The same applies to the telephone numbers, they may be the same for each division or address, or they may be different.

I have considered several approaches to this, but do not know the best to use?

a) I could have one big table, which would replicate much of the data many times.

b) I have开发者_Python百科 considered holding the FK of the court in the division table, and the FK of the division in the address table. This means duplicating division values for each court in the division table. I'm then not sure what to do with the telephone table.

c) My final though was to have a single link table holding the FK's all all 4 other tables. This would mean no replication of data, but would mean a lot of joins to get anything meaningful out, and would probably be unwieldy to use.

I'm sure I'm overlooking the obvious here, but what is the best way to model these tables?

The tables will be accessed through CakePHP, but perhaps we'll leave that to one side for the moment.

If you will have less than 10,000 rows counting all combinations of the four tables I would suggest just a flat single table structure. The data size would be around 80 meg if the row size was about 8K. Most free or low cost databases will hold that much in memory and place your I/O cost to near zero.

If you feel you want multiple tables then here is a design that should give you flexibility to expand your design. You would add application specific columns to the tables shown.

The TypeList table could hold the types of courts such as State, Federal, Local with the entries being keyed by the TL_Table value of "Court". The list of divisions would be stored here with the TL_Type of "Division". Using the TL_Type one could populate the drop down list for data entry. The type list for telephone numbers might be on-call, fax, primary, front desk, clerk, sherrif, etc.

Which ever way you decide I am sure someone will have a screaming negative comment but if it works they will just be screeming in space.

what you describe is a star schema and is the advised way to do it

court 1:m divisions
divisions 1:m addresses
address 1:m  telephone

i think you should look at what queries you need to do to get the data out as you need it although just a description not sql would be the first step