Relational Database Join types

[WindandWater]

Hello,

I have a seemingly simple question:
In general when joining tables I assume we generally use
the default option which is an inner joing/ both tables joined on matching
records.

I have a few tables which have many to one, one to many relationships
and also a many to many table. I have some look up table which I don't
use refrential integrity

My Parent table is Quicksheet and my related related tables have
data that could exist or may not.

How do I define relationships that have a many to one/ one to many
relationship
but don't require the data every time I create a new record? From what I
have read
it seems that using referential integrity helps to make sure there are no
orphan
records but maybe it okay in my case?


Below is the main table tblQuicksheets:

tblQuicksheet:
quicksheetID PK (linked to junction table)
jobID Fk (linked to tblPLApackage)
enviroID Fk (look-up)
facilityID FK (look-up)
systemID Fk (look-up)
wireCenterID FK (look-up)
maintPriority
quickPriorityID FK (look-up)
quickStatusID FK (look-up)
techID
distArea
location
remarks
hazardID
platID FK
facilityID FK (look-up)


tblJunctionCableQS: (junction table)
junctionID
cableID FK
quicksheetID Fk

tblCables:
cableID PK
cableName
lowPR
highPR
cableLength
cableType

tblPLApackage:
plaPackageID
plapackageNum
jobID FK

 

[Fred]

Here's one way to look at it.

In the end the purpose of joins is to accomplish the task that you define
for them (usually to produce information) and to the driving quesiotn is
"what do you want to do/produce?) The nature of the data (one to many,
many to many etc.) imposes certain constraints and requirements. So,
strictly speaking you have to decide and tell us that before you can get an
answer.

In a many to many relationship, usually the only way to join that produces
anything useful is with a junction table and inner joins, so that would
pretty much determine your m to m design.

Referential integrity implements implements a decision made by YOU that
every record in table A must have a matching record in table B. In my
opinion you only have a few places where you need this.

 

[WindandWater]

So, I what I am creating is a database for capturing maintenance tickets
(Quicksheets).
For each Quicksheet there could be a job created to fix it,
so I think here is a one to many join, no referential integrity since its
not always present.

There is a facility type that has serveral types and if it is a cable it
would
need to be added. Currently I have my tblCable related to the QuicksheetID.
Now I am thinking that it should probably be related to the facilityID. Is
it possible to
have a field (facilityID) which is related to two tables? One is a look up
table (tblFaciityLU)
and the other would be to (tblJunctionCableQS) which is related to
(tblCable) to store cable data.
In this set of table since it is not always required, I would also remove
referential integrity.

Most of my table appear to be just look up tables.
I want to make sure I am using my look up table correctly.
If I turn on referential integrity does that force the user to choose
a field selection from the look up table or is this just controled from
the field properties under required field or not?

 

[Fred]

There are a zillion scenerios and methods for lookups. Some of them I'm not
very good at and others I often do in a way that developers don't recommend.
So I better leave that one for somebody else.

 

[David Portas]

So, I what I am creating is a database for capturing maintenance tickets
(Quicksheets).
For each Quicksheet there could be a job created to fix it,
so I think here is a one to many join, no referential integrity since its
not always present.

Therefore you actually have TWO types of Quicksheet: one with an associated
job and one without. For the one with a job you certainly can and probably
should enforce the referential integrity requirement (you wouldn't want a
Quicksheet referencing a job that didn't exist would you?).

Hypothetical SQL example:

CREATE TABLE QuickSheet
(QuickSheetID INT NOT NULL PRIMARY KEY, ...);

CREATE TABLE QuickSheetJob
(QuickSheetID INT NOT NULL PRIMARY KEY REFERENCES QuickSheet
(QuickSheetID),
JobNum INT NOT NULL REFERENCES Job (JobNum),
...);