Sfrgttk

Why do Australian milk farmers need to protest supermarkets' milk price?

How could a female member of a species produce eggs unto death?

Make a transparent 448*448 image

Is all copper pipe pretty much the same?

Single word request: Harming the benefactor

Time travel short story where dinosaur doesn't taste like chicken

When is a batch class instantiated when you schedule it?

Does Linux have system calls to access all the features of the file systems it supports?

Potentiometer like component

Should we release the security issues we found in our product as CVE or we can just update those on weekly release notes?

Decoding assembly instructions in a Game Boy disassembler

Why does Deadpool say "You're welcome, Canada," after shooting Ryan Reynolds in the end credits?

Latest web browser compatible with Windows 98

Time dilation for a moving electronic clock

What Happens when Passenger Refuses to Fly Boeing 737 Max?

What does it mean when multiple 々 marks follow a 、?

What is the difference between "shut" and "close"?

validation vs test vs training accuracy, which one to compare for claiming overfit?

This equation is outside the page, how to modify it

Silly Sally's Movie

Is it true that real estate prices mainly go up?

Is going from continuous data to categorical always wrong?

What happens with multiple copies of Humility and Glorious Anthem on the battlefield?

How does Dispel Magic work against Stoneskin?

Avoid circular reference with configuration tables

Database Design: Linking 'vague' entries to associations?Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaningPrecautions when designing database schema if tables are going to have billons of rowsWhen normalizing a relational database, should multiple “type” tables be combined into one?How to avoid a cyclic dependency (circular reference) between 3 tables?How can I avoid this circular relation?How to design parent, child, photos relationships in mysql when photos can belong to either parent or child?Ms-access defining additional Index FieldsDatabase Table Schema Circular Reference issueIs the modeling technique changing with column-oriented databases?

2

I've got a few tables that define configuration information. This defines what combination are allowed in the system.

However, I've got a circular reference on ProjectName which can cause potential invalid relationships. I'm wondering how I can enforce this better in the database. I don't want to trust the code to enforce it.

Table Schema (Table / Columns)

Project

    ProjectName



Category

    CategoryName



ProjectCategory

    ProjectCategoryID

    ProjectName

    CategoryName



Inventory

    ProjectName

    ProjectCategoryID

Populated Tables (Table/Column/Data)

Project

    ProjectName

        A

        B



Category

    CategoryName

        X

        Y



ProjectCategory

    ProjectCategoryID, ProjectName, ProjectCategory

        1, A, X

        2, A, Y

        3, B, Y



Inventory

    ProjectName, ProjectCategoryID

        A, 1

        A, 3

Inventory record (A, 3) is invalid. The inventory belongs to project 'A' yet the configuration belongs to project 'B'. Because of the circular reference, its possible to associate a record incorrectly.

database-design

share|improve this question

edited Apr 20 '16 at 22:26

csdev

asked Apr 20 '16 at 21:27

csdevcsdev

112

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is there a reason why you don't define a ProjectID and use ProjectName everywhere as an identifier instead?
  
  – Ziggy Crueltyfree Zeitgeister
  Apr 21 '16 at 0:38
  

  
  
  
  

add a comment | 

2

I've got a few tables that define configuration information. This defines what combination are allowed in the system.

However, I've got a circular reference on ProjectName which can cause potential invalid relationships. I'm wondering how I can enforce this better in the database. I don't want to trust the code to enforce it.

Table Schema (Table / Columns)

Project

    ProjectName



Category

    CategoryName



ProjectCategory

    ProjectCategoryID

    ProjectName

    CategoryName



Inventory

    ProjectName

    ProjectCategoryID

Populated Tables (Table/Column/Data)

Project

    ProjectName

        A

        B



Category

    CategoryName

        X

        Y



ProjectCategory

    ProjectCategoryID, ProjectName, ProjectCategory

        1, A, X

        2, A, Y

        3, B, Y



Inventory

    ProjectName, ProjectCategoryID

        A, 1

        A, 3

Inventory record (A, 3) is invalid. The inventory belongs to project 'A' yet the configuration belongs to project 'B'. Because of the circular reference, its possible to associate a record incorrectly.

database-design

share|improve this question

edited Apr 20 '16 at 22:26

csdev

asked Apr 20 '16 at 21:27

csdevcsdev

112

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is there a reason why you don't define a ProjectID and use ProjectName everywhere as an identifier instead?
  
  – Ziggy Crueltyfree Zeitgeister
  Apr 21 '16 at 0:38
  

  
  
  
  

add a comment | 

I've got a few tables that define configuration information. This defines what combination are allowed in the system.

However, I've got a circular reference on ProjectName which can cause potential invalid relationships. I'm wondering how I can enforce this better in the database. I don't want to trust the code to enforce it.

Table Schema (Table / Columns)

Project

    ProjectName



Category

    CategoryName



ProjectCategory

    ProjectCategoryID

    ProjectName

    CategoryName



Inventory

    ProjectName

    ProjectCategoryID

Populated Tables (Table/Column/Data)

Project

    ProjectName

        A

        B



Category

    CategoryName

        X

        Y



ProjectCategory

    ProjectCategoryID, ProjectName, ProjectCategory

        1, A, X

        2, A, Y

        3, B, Y



Inventory

    ProjectName, ProjectCategoryID

        A, 1

        A, 3

Inventory record (A, 3) is invalid. The inventory belongs to project 'A' yet the configuration belongs to project 'B'. Because of the circular reference, its possible to associate a record incorrectly.

database-design

share|improve this question

edited Apr 20 '16 at 22:26

csdev

asked Apr 20 '16 at 21:27

csdevcsdev

112

Project

    ProjectName



Category

    CategoryName



ProjectCategory

    ProjectCategoryID

    ProjectName

    CategoryName



Inventory

    ProjectName

    ProjectCategoryID

Project

    ProjectName

        A

        B



Category

    CategoryName

        X

        Y



ProjectCategory

    ProjectCategoryID, ProjectName, ProjectCategory

        1, A, X

        2, A, Y

        3, B, Y



Inventory

    ProjectName, ProjectCategoryID

        A, 1

        A, 3

share|improve this question

edited Apr 20 '16 at 22:26

csdev

asked Apr 20 '16 at 21:27

csdevcsdev

112

share|improve this question

edited Apr 20 '16 at 22:26

csdev

asked Apr 20 '16 at 21:27

csdevcsdev

112

asked Apr 20 '16 at 21:27

csdevcsdev

112

asked Apr 20 '16 at 21:27

csdevcsdev

112

1 Answer
1

active

oldest

votes

0

You can solve this problem by either removing ProjectName from Inventory (since it is redundant because it is already specified in ProjectCategory), or enforce the relationship via a foreign key:

CREATE UNIQUE INDEX ON ProjectCategory (ProjectCategoryID, ProjectName);



ALTER TABLE Inventory ADD

  FOREIGN KEY (ProjectCategoryID, ProjectName) REFERENCES (ProjectCategoryID, ProjectName);

share|improve this answer

answered Apr 21 '16 at 0:40

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2651819

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f135992%2favoid-circular-reference-with-configuration-tables%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

You can solve this problem by either removing ProjectName from Inventory (since it is redundant because it is already specified in ProjectCategory), or enforce the relationship via a foreign key:

CREATE UNIQUE INDEX ON ProjectCategory (ProjectCategoryID, ProjectName);



ALTER TABLE Inventory ADD

  FOREIGN KEY (ProjectCategoryID, ProjectName) REFERENCES (ProjectCategoryID, ProjectName);

share|improve this answer

answered Apr 21 '16 at 0:40

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2651819

add a comment | 

0

You can solve this problem by either removing ProjectName from Inventory (since it is redundant because it is already specified in ProjectCategory), or enforce the relationship via a foreign key:

CREATE UNIQUE INDEX ON ProjectCategory (ProjectCategoryID, ProjectName);



ALTER TABLE Inventory ADD

  FOREIGN KEY (ProjectCategoryID, ProjectName) REFERENCES (ProjectCategoryID, ProjectName);

share|improve this answer

answered Apr 21 '16 at 0:40

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2651819

add a comment | 

You can solve this problem by either removing ProjectName from Inventory (since it is redundant because it is already specified in ProjectCategory), or enforce the relationship via a foreign key:

CREATE UNIQUE INDEX ON ProjectCategory (ProjectCategoryID, ProjectName);



ALTER TABLE Inventory ADD

  FOREIGN KEY (ProjectCategoryID, ProjectName) REFERENCES (ProjectCategoryID, ProjectName);

share|improve this answer

answered Apr 21 '16 at 0:40

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2651819

CREATE UNIQUE INDEX ON ProjectCategory (ProjectCategoryID, ProjectName);



ALTER TABLE Inventory ADD

  FOREIGN KEY (ProjectCategoryID, ProjectName) REFERENCES (ProjectCategoryID, ProjectName);

share|improve this answer

answered Apr 21 '16 at 0:40

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2651819

answered Apr 21 '16 at 0:40

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2651819

answered Apr 21 '16 at 0:40

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2651819