Many tables or many rows? Which is best?

[haven104]

I'm currently in the design phase of a new database to hold geological drillhole logs.

There is approximately 30,000 drillholes. A drillhole table will have a hole id, easting and northing, elevation and other general unique data.

Each drillhole log contains the lithological unit (rock unit) intersected, the top depth and bottom depth of each unit, the name and description of each unit.

The amount of data will increase over the years as more holes are drilled.

So now to the crunch - is it better to have a table for each drillhole log with fields for lithological unit, depths etc (Which means Access may eventually hit its max number of tables)?
Or is it better to have one log table which has the hole id as a field along with lith unit, depth etc (And hit the max number of rows per table)? Would one table with multiple hole id rows be redundant data?

Cheers
Tom

 

[SQLBI]

Tom,

I would suggest keeping the number of tables to a minimum and not worry too much about the number of rows. Access can take quite a beating and can be upsized to SQL server if necessary.

Redundant data doesn't seem like too much of an issue here as each record in your database is pretty unique.

I would have tblDrillHole table, made up of;

DrillHoleID (PK)
Easting
Northing
Elevation
LithologicalID (FK)

tblLithologicalUnit

LithologicalID (PK)
LithoName
LithoDescription
TopDepth
BottomDepth

When it's broken down like this it's actually a very simple database, with little need for any further normalisation due to almost every field in the db being unique to that record...

Hope this helps,

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[KenReay]

Hi

I would have a single log table with a drillholeId, if you have seperate tables (each names differently you will also have to have 'n' versions of all queries, etc which reference the tables.

If you are concerned about volumne, you could have a database per Project, but again personally I would cuastion against this since ther eis a danger that you will increase teh maintenance work load by duplicating databases.

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SUnderwood]

Tom,

May I add to Leigh Moore's suggestion an additional table which may be of use to you. That is 'Samples'.

I presume at regular intervals of depth a result is recorded, or Sample taken, of, say, ther soil geology or perhaps it is bagged and sent back to a depot for storage and future lab testing.

If so, the Sample table would be linked to the Hole table using a composit primary key, seconded on Elevation.


tlbSample
DrillHoleID (PK)
Elevation (PK)
{other data}

tblHoles
DrillHoleID (PK)
LithologicalID (FK)
Easting
Northing


This arrangement carries great benefits (if required) and, with a little tweaking, also facilitates Sample track in the stoage depot and future automation of requesting samples for lab tests.

Just a thought.

Sean

 

[JeremyNYC]

Tom,

I'm sure others around here are sick of me refering people to this article, but I've got a copy of Paul Litwin's "Fundamentals of Relational Database Design" on my website. It has proven helpful to lots of folks in your shoes.

Leigh, Ken, and Sean's suggestions all sound rock solid to me (sorry, I couldn't resist). But it will also be good to have a general foundation in the basic theory behind designing databases.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[OhioSteve]

I would agree with JeremyNYC. You need to review a basic source of information about relational databases. Then you will be in a much better position to ask questions.

Leighmoore's schema is okay, but you need to understand WHY he did what he did.

 

[willir]

Jeremy

I never get sick of poeple spending time on pre-planning their database. I do get tired of people who have oodles of data and then realize they have a bad database design, and can not get the information they want. You have been contributing to this forum for quite a long time with a proven track record.

haven104 [\b]
Clarification - number of records not tables. A record is an entry into a table. A table is an object in the database.

Per your question which is better, rephrased, many records and several tables, or a few less tables and many large records...

With a properly designed database, you eliminate redundancy which means you save space (okay, okay each table has an index or indexes and indexes use up space but you know what I mean).

So an effecient table tends to be better than a large "flat" table with empty fields or duplicate data.

Lastly, I suggest to "play".

Jeremy's link gives good advice, but since you will be making a conisderable investment in this database, take the time to play with the tables to see how the data will be stored. Make sure you can retrieve the information you need. From my perspective, this is the primary reason for a database, turn data into useful information.

Richard

 

[SUnderwood]

Jeremy

Might I suggest that you place the "Fundamentals of Relational Database Design" article on the FAQ page?

It horrifies me to know that in a forum (site!) that deals with the absolute fundamentals of DB design that there is not FAQ on Normalisation, Entities Relationship Diagramming, and basic implementation consideration (such as meaningful PKs and not the ghastly AutoNumber).

With these few resources a lot of unnecessary gabber can be alleviated with the four letter acronym 'RTFM', and more constructive communication can progress.


Sean

International Applications Development
Business and Data Anyalist
Database & Database Applications
Web-Applications
VB, VBA, ASP, SQL, SSADM
email:sean.underwood1@hotmail.com

 

[KenReay]

Hi

With respect to all of the excellent comments and advice regarding database design, normalisation etc, I think we have lost the thread so to speak. I think the oginal questions concerned the fact that the sets of data were related to particular 'projects' where a particular drillhole is a 'project'. I think the orginal question was "should I have a table for each projects dataset, or should I have a single table containing the data for all projects, with a column which identifies the project to which the row belongs" I think the answer is single table with a column to identify which project each row belongs to.

Or is it me who has lost the plot?

Puzzled

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JeremyNYC]

Ken,

Spot on.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[SUnderwood]

So where is Tom?

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
Interested parties please email: seanunderwood1@hotmail.com

 

[SUnderwood]

or haven104 ?

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
Interested parties please email: seanunderwood1@hotmail.com