Avoid circular reference with configuration tablesDatabase Design: Linking 'vague' entries to...
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?
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
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.
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
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
asked Apr 20 '16 at 21:27
csdevcsdev
112
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
database-design
asked Apr 20 '16 at 21:27
csdevcsdev
112
asked Apr 20 '16 at 21:27
csdevcsdev
112
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
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.
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 |
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
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
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 |
1 Answer
1
active
oldest
votes
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);
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
});
}
});
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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);
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);
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);
answered Apr 21 '16 at 0:40
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2651819
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);
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
answered Apr 21 '16 at 0:40
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2651819
4,2651819
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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