Duplicate tables within DB 1

[nickjar2]

I will try and explain the situation:

I have a Sites table which holds data relating to a particular site ie. name and address
I also have Loggers, Meters and Correctors tables.

The site can have many Loggers, many Meters and many Correctors, therefore i have set up my relationsips accordingly. However, i also need a faults table. There can be a fault(s) with a Logger, Meter or Corrector.
Should i create ONE faults table, or have ONE faults table per table?

If this doesn't make sense, pls let me know, and i will try to be more specific. Nick (Everton Rool OK!)

 

[BanditWk]

In the Faults Table, I'm assuming that you're going to want to list all the faults per record in the Sites Table - if this is the case, you need a Distinct Field that you can relationship to via One-To-Many. It looks like you only need one additional table (Faults). Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

 

[LittleSmudge]

The thing that is not clear in your post is - do the faults relate to the SITE or to the Loggers, Meters & Correctors.

If the latter, will the faults with Loggers need the same fields stored as faults with Meters, Correctors etc.

If there are a lot of unique field then it's simle - THREE Faults tables LFaults, MFaults and CFaults.

However if the fields are the same then it becomes much more a question of what you'll do with the data later. If there is a benefit of having them all in one table - for reporting, analysis etc then keep them in one table. If not then - table count is effectivly FREE so go make 3 tables.

If you have three table :-
LFault will need a foreign key holding the Logger table's primary key.
MFault will need a foreign key holding the Meter table's primary key.
CFault will need a foreign key holding the Corrector table's primary key.


If you go with a single table you'll have to have either 3 Foreign Key fields, only one of which will be valid in any record ( That should start alarm bells ringing )
Or ONE Foreign Key field and an Allocation field that contains either:-
"Logger", "Meter" or "Corrector" ( Or 1,2,3 if you wish ) to tell the system which table this record is the Fault for.



'ope-that-'elps.

G LS

 

[nickjar2]

Cheers chaps!

The faults could be for a logger, meter or correctors, but never a site.

I was thinking on the lines of 3 fault tables.

However, there won't be any unique fields, apart from the foriegn key from each of the loggers, meters etc

Reporting purposes, it would probably be easier to have ONE table, but as u have mentioned, 3 foreign keys, where only one will get filled in per record, which like mentioned above, does not sound too good.

I am probably inclined to for 3 tables, I was just a little concerned about my 'relational' db. I am converting from approach (no relationships at all, everything bunged into the 1 table) and then into Oracle, and i wanted a good db design b4 i started coding etc.

I will leave it in your hands. My coding is good, and my db design is generally good, but sometimes, i need a little help. ) Nick (Everton Rool OK!)

 

[nickjar2]

Another thing,

Or ONE Foreign Key field and an Allocation field that contains either:-
"Logger", "Meter" or "Corrector" ( Or 1,2,3 if you wish ) to tell the system which table this record is the Fault for.

With that though, I wouldn't be able to set up ref integ, or would I? Nick (Everton Rool OK!)

 

[LittleSmudge]

No Nick, One FK and an allocation field precludes referential integrity.

If you do go for 3 tables then you can always UNION the three together into one big table ( sans the PrimeKeyField ) when it comes to reporting Error types etc.


'ope-that-'elps.

G LS

 

[nickjar2]

okerly dokerly then Smudge.

I think I will try the 3 table approach. The only problem I will have is migrating the data from the ONE table into the 3 new tables, because the only field that states whether it is a meter, logger fault, is a text field in the old db.

Cheers again )

ps. if i get stuck again, i know who to turn to. ) Nick (Everton Rool OK!)

 

[LittleSmudge]

That shouldn't be too big a problem.

I'd do that in a Recordset
Open a recordset on the source and cycle through it one record at a time
Within that look AddNew to another recordset on the target table ( One recordset per table ) and write the date.
You can do a SELECT CASE on the text field to decide which table the Error data recordset dhould open onto.


( If you need guidance on this then I'll need to know if you're using DAO or ADO { Up to Access 97 or beyond. )


'ope-that-'elps.

G LS

 

[nickjar2]

I am hoping this text field will specifically say whether corrector, meter or logger. If that is the case, I will maybe do an update query.
I am not too keen on this normalisation. I prefer coding.

Cheers again! Nick (Everton Rool OK!)

 

[LittleSmudge]

I am not too keen on this normalisation. I prefer coding.

What ?

I didn't understand that line at all.

Normalisation is about the design of the database to ensure data entegrity and reliability. Everything we've discussed is within the spirit and intent of Normalisation theory.
By taking the flat file data and converting it into a relational database you are improving the 'Normalisation' automatically.
Don't get too hung up on every last bit of Normalisation though. Most dbase analysts will break Normalisation rules from time to time and for very good reasons. Consciously doing it for a reason is okay ( as long as What & Why is documented ). Breaking Normalisation rules because you hadn't thought about it is the big bad wolf.

As for Coding:-
Coding is just the how you go about it.


G LS

 

[nickjar2]

What it is, when I say I don't like normalisation, is that I find it difficult, especially when i am trying to normalise a database that I know nothing about.
I am contracting for a gas company, and I still don't understand everything therre is to know about it, therefore, trying to normalise the data can be a bit tricky.

Also, i don't do much db design, I normally right code for apps, where there usually isn't a database invlolved, so I probably need more practice in the design bit. ) Nick (Everton Rool OK!)