If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relational Database Join types
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 |
#2
|
|||
|
|||
Relational Database Join types
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. |
#3
|
|||
|
|||
Relational Database Join types
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" wrote: 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. |
#4
|
|||
|
|||
Relational Database Join types
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. |
#5
|
|||
|
|||
Relational Database Join types
"WindandWater" wrote in message
... 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), ...); -- David Portas |
Thread Tools | |
Display Modes | |
|
|