One table or a few?

[coastguard]

I'm setting up a data base so a friend can use it in a new job. He will be inspecting and writing reports on about 360 stations.

The stations are a part of a large organisation divided into Region (3), Area's (9), sectors(65) and then stations. In order to use linked combo boxes to drill down to each station (one to select from the Region, One to select from that Regions area's etc), is it better to have one table listing all the data for the query to use, or use more than one table.

Reading this it sounds long winded to describe what I want to know but hope someone will offer a solution.

 

[neileg]

You haven't told us much about the data being stored, but it's almost certain that one table is preferable.

 

[coastguard]

Thanks Neil.

The data to be stored (in another table) will be the Station name, Date, Y/N for whether an inspection has been done, Pass/Fail, comment and possibly some other stuff I don't yet know about.

I'll link the Tables on Station name so that I can get some reports based around Region or Area.

 

[neileg]

Still not a lot to go on!

If you are asking about the geodata, then I would expect the station table to be linked to a table of sectors, for the sectors to be linked to areas and the areas to be linked to regions. But for this to work, the sectors will need to be unique. If you have Sectors that are numbered, e.g.

Area      Sector
Blue        1
Blue        2
Red         1
Red         2
etc.

then you will need to combine area and sector, otherwise you wouldn't know which sector 2 you want.

 

[coastguard]

Thanks again. I don't think I'm doing a good job of explaining or maybe I'm missing something that's obvious to you.

If I had one table which listed; (I presume this is what you mean by geodata)
RegionName; AreaName; SectorName; StationName;
Region1, AreaSW, SectorNCornwall, StationBude,
Region1, AreaSW, SectorNCornwall, StationBoscastle,
Region1, AreaNW, SectorBideford, StationHartland,
Region2 AreaHumber, SectorHartlepool, StationAmble,

I would presumably write a query for each combo box on the form to enable me to drill through Region, Area etc and arrive at the station to be inspected. The data for the inspection would be held in a table "TblInspectionDone" and the link between these two tables would be on Station.

I could put all the geodata into 3 separate tables ensuring that there was the link between the relevant Region and Areas, Area and Sectors and Sectors and staions. Which brings me to the original question;
Is it better to have one table with the geodate or split it into 3?

 

[neileg]

I'd drifted towards that understanding, eventually. Repeating data in one table as set out in your post is normally not good design and unless there is some compelling reason to do this, I would always use three tables. This would make renaming or reorganisation much easier to reflect in your data.

 

[coastguard]

Thanks Neil.

I'd made a start following your last message and realised that it is best to use the three table as you suggested.

Hope the weather in Newcastle is as good as here in Cornwall.

 

[neileg]

Aye, not bad today. A bit overcast but no frost.

 

[jdraw]

Thanks Neil.

I'd made a start following your last message and realised that it is best to use the three table as you suggested.

Hope the weather in Newcastle is as good as here in Cornwall.

Further to Neil's comments you may wish to refer to this link on database structure.
http://r937.com/relational.html
Good luck.

 

[coastguard]

Thanks JDraw, am printing it off now