How to design Access database table where only one of two fields will have data?
I am creating an Access database, with a SALES table that relates to an ITEMS table, which contain the services and supplies purchased. Each record in the ITEMS table will have either a supplies or service name, each in their own respective table with the pricing and related information. How can I create this ITEMS table in Access to allow one the two fields to be null for each record? Will this create errors when querying this table?
sql database ms-access database-design
edited Nov 14 '18 at 7:52
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 14 '18 at 0:38
Hoss Hoss
32
add a comment |
I am creating an Access database, with a SALES table that relates to an ITEMS table, which contain the services and supplies purchased. Each record in the ITEMS table will have either a supplies or service name, each in their own respective table with the pricing and related information. How can I create this ITEMS table in Access to allow one the two fields to be null for each record? Will this create errors when querying this table?
sql database ms-access database-design
edited Nov 14 '18 at 7:52
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 14 '18 at 0:38
Hoss Hoss
32
I don't think there is a way to do this within MS Access. It supports neither check constraints nor triggers.
– Gordon Linoff
Nov 14 '18 at 4:10
2
@Gordon Microsoft Access certainly supports check constraints (when executed in ANSI 92 mode or over an OLEDB connection), and supports data macros which are pretty close to triggers.
– Erik von Asmuth
Nov 14 '18 at 6:30
add a comment |
I am creating an Access database, with a SALES table that relates to an ITEMS table, which contain the services and supplies purchased. Each record in the ITEMS table will have either a supplies or service name, each in their own respective table with the pricing and related information. How can I create this ITEMS table in Access to allow one the two fields to be null for each record? Will this create errors when querying this table?
sql database ms-access database-design
edited Nov 14 '18 at 7:52
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 14 '18 at 0:38
Hoss Hoss
32
I am creating an Access database, with a SALES table that relates to an ITEMS table, which contain the services and supplies purchased. Each record in the ITEMS table will have either a supplies or service name, each in their own respective table with the pricing and related information. How can I create this ITEMS table in Access to allow one the two fields to be null for each record? Will this create errors when querying this table?
sql database ms-access database-design
sql database ms-access database-design
edited Nov 14 '18 at 7:52
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 14 '18 at 0:38
Hoss Hoss
32
edited Nov 14 '18 at 7:52
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 14 '18 at 0:38
Hoss Hoss
32
edited Nov 14 '18 at 7:52
Brian Tompsett - 汤莱恩
4,2031338101
edited Nov 14 '18 at 7:52
Brian Tompsett - 汤莱恩
4,2031338101
edited Nov 14 '18 at 7:52
Brian Tompsett - 汤莱恩
4,2031338101
4,2031338101
asked Nov 14 '18 at 0:38
Hoss Hoss
32
asked Nov 14 '18 at 0:38
Hoss Hoss
32
asked Nov 14 '18 at 0:38
Hoss Hoss
32
32
I don't think there is a way to do this within MS Access. It supports neither check constraints nor triggers.
– Gordon Linoff
Nov 14 '18 at 4:10
2
@Gordon Microsoft Access certainly supports check constraints (when executed in ANSI 92 mode or over an OLEDB connection), and supports data macros which are pretty close to triggers.
– Erik von Asmuth
Nov 14 '18 at 6:30
add a comment |
I don't think there is a way to do this within MS Access. It supports neither check constraints nor triggers.
– Gordon Linoff
Nov 14 '18 at 4:10
2
@Gordon Microsoft Access certainly supports check constraints (when executed in ANSI 92 mode or over an OLEDB connection), and supports data macros which are pretty close to triggers.
– Erik von Asmuth
Nov 14 '18 at 6:30
I don't think there is a way to do this within MS Access. It supports neither check constraints nor triggers.
– Gordon Linoff
Nov 14 '18 at 4:10
I don't think there is a way to do this within MS Access. It supports neither check constraints nor triggers.
– Gordon Linoff
Nov 14 '18 at 4:10
2
2
@Gordon Microsoft Access certainly supports check constraints (when executed in ANSI 92 mode or over an OLEDB connection), and supports data macros which are pretty close to triggers.
– Erik von Asmuth
Nov 14 '18 at 6:30
@Gordon Microsoft Access certainly supports check constraints (when executed in ANSI 92 mode or over an OLEDB connection), and supports data macros which are pretty close to triggers.
– Erik von Asmuth
Nov 14 '18 at 6:30
add a comment |
1 Answer
1
active
oldest
votes
In essence, there are 3 things you can do:
Don't worry about it
Access can query rows where one of the columns is null just fine, there's no reason data needs to be entered in a column, and no need for errors to pop up. The only real limitation is that you can't add a unique index since the data for the column is not unique.
Enforce a constraint to let one of the columns be null
You can add a check constraint to force one of the fields to be null. Since this is a table level check constraint, you don't even need to do complicated things to add it. Just open up the table in design view, open the property pane, and in the Validation rule, add
[SupplyName] Is Null Or [ServiceName] Is Null
Instead of storing the supply name and service name in separate columns, store them in a single one, and use a second column to specify what kind of name is stored in that column.
I strongly recommend to go with option 3. It will require less storage (since the specifier column can be small), allows for a unique composite index that can increase performance when joining, and is way more extensible (if you'd have a third type of name, you can add that without modifying indexes or table design).
Disadvantages of approach 3 is that the Access query designer doesn't support joins with literals specifying on which kind of name you're joining. For inner joins, that's easily fixed by just specifying in the WHERE clause. Also, Access won't be able to enforce referential integrity for relationships like this one.
answered Nov 14 '18 at 8:26
Erik von AsmuthErik von Asmuth
19.1k62138
Option 3 seems like the best approach. So essentially, one column can hold two foreign keys in the ITEMS table?
– Hoss
Nov 14 '18 at 20:44
Yes, indeed. The second column determines to which column the foreign key refers. In one of my databases, I have a single column that's the foreign key to 6 tables, with a second column specifying which table it refers to. Using 6 columns instead would increase the size of that table significantly
– Erik von Asmuth
Nov 14 '18 at 21:06
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53291520%2fhow-to-design-access-database-table-where-only-one-of-two-fields-will-have-data%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
In essence, there are 3 things you can do:
Don't worry about it
Access can query rows where one of the columns is null just fine, there's no reason data needs to be entered in a column, and no need for errors to pop up. The only real limitation is that you can't add a unique index since the data for the column is not unique.
Enforce a constraint to let one of the columns be null
You can add a check constraint to force one of the fields to be null. Since this is a table level check constraint, you don't even need to do complicated things to add it. Just open up the table in design view, open the property pane, and in the Validation rule, add
[SupplyName] Is Null Or [ServiceName] Is Null
Instead of storing the supply name and service name in separate columns, store them in a single one, and use a second column to specify what kind of name is stored in that column.
I strongly recommend to go with option 3. It will require less storage (since the specifier column can be small), allows for a unique composite index that can increase performance when joining, and is way more extensible (if you'd have a third type of name, you can add that without modifying indexes or table design).
Disadvantages of approach 3 is that the Access query designer doesn't support joins with literals specifying on which kind of name you're joining. For inner joins, that's easily fixed by just specifying in the WHERE clause. Also, Access won't be able to enforce referential integrity for relationships like this one.
answered Nov 14 '18 at 8:26
Erik von AsmuthErik von Asmuth
19.1k62138
Option 3 seems like the best approach. So essentially, one column can hold two foreign keys in the ITEMS table?
– Hoss
Nov 14 '18 at 20:44
Yes, indeed. The second column determines to which column the foreign key refers. In one of my databases, I have a single column that's the foreign key to 6 tables, with a second column specifying which table it refers to. Using 6 columns instead would increase the size of that table significantly
– Erik von Asmuth
Nov 14 '18 at 21:06
add a comment |
In essence, there are 3 things you can do:
Don't worry about it
Access can query rows where one of the columns is null just fine, there's no reason data needs to be entered in a column, and no need for errors to pop up. The only real limitation is that you can't add a unique index since the data for the column is not unique.
Enforce a constraint to let one of the columns be null
You can add a check constraint to force one of the fields to be null. Since this is a table level check constraint, you don't even need to do complicated things to add it. Just open up the table in design view, open the property pane, and in the Validation rule, add
[SupplyName] Is Null Or [ServiceName] Is Null
Instead of storing the supply name and service name in separate columns, store them in a single one, and use a second column to specify what kind of name is stored in that column.
I strongly recommend to go with option 3. It will require less storage (since the specifier column can be small), allows for a unique composite index that can increase performance when joining, and is way more extensible (if you'd have a third type of name, you can add that without modifying indexes or table design).
Disadvantages of approach 3 is that the Access query designer doesn't support joins with literals specifying on which kind of name you're joining. For inner joins, that's easily fixed by just specifying in the WHERE clause. Also, Access won't be able to enforce referential integrity for relationships like this one.
answered Nov 14 '18 at 8:26
Erik von AsmuthErik von Asmuth
19.1k62138
Option 3 seems like the best approach. So essentially, one column can hold two foreign keys in the ITEMS table?
– Hoss
Nov 14 '18 at 20:44
Yes, indeed. The second column determines to which column the foreign key refers. In one of my databases, I have a single column that's the foreign key to 6 tables, with a second column specifying which table it refers to. Using 6 columns instead would increase the size of that table significantly
– Erik von Asmuth
Nov 14 '18 at 21:06
add a comment |
In essence, there are 3 things you can do:
Don't worry about it
Access can query rows where one of the columns is null just fine, there's no reason data needs to be entered in a column, and no need for errors to pop up. The only real limitation is that you can't add a unique index since the data for the column is not unique.
Enforce a constraint to let one of the columns be null
You can add a check constraint to force one of the fields to be null. Since this is a table level check constraint, you don't even need to do complicated things to add it. Just open up the table in design view, open the property pane, and in the Validation rule, add
[SupplyName] Is Null Or [ServiceName] Is Null
Instead of storing the supply name and service name in separate columns, store them in a single one, and use a second column to specify what kind of name is stored in that column.
I strongly recommend to go with option 3. It will require less storage (since the specifier column can be small), allows for a unique composite index that can increase performance when joining, and is way more extensible (if you'd have a third type of name, you can add that without modifying indexes or table design).
Disadvantages of approach 3 is that the Access query designer doesn't support joins with literals specifying on which kind of name you're joining. For inner joins, that's easily fixed by just specifying in the WHERE clause. Also, Access won't be able to enforce referential integrity for relationships like this one.
answered Nov 14 '18 at 8:26
Erik von AsmuthErik von Asmuth
19.1k62138
In essence, there are 3 things you can do:
Don't worry about it
Access can query rows where one of the columns is null just fine, there's no reason data needs to be entered in a column, and no need for errors to pop up. The only real limitation is that you can't add a unique index since the data for the column is not unique.
Enforce a constraint to let one of the columns be null
You can add a check constraint to force one of the fields to be null. Since this is a table level check constraint, you don't even need to do complicated things to add it. Just open up the table in design view, open the property pane, and in the Validation rule, add
[SupplyName] Is Null Or [ServiceName] Is Null
Instead of storing the supply name and service name in separate columns, store them in a single one, and use a second column to specify what kind of name is stored in that column.
I strongly recommend to go with option 3. It will require less storage (since the specifier column can be small), allows for a unique composite index that can increase performance when joining, and is way more extensible (if you'd have a third type of name, you can add that without modifying indexes or table design).
Disadvantages of approach 3 is that the Access query designer doesn't support joins with literals specifying on which kind of name you're joining. For inner joins, that's easily fixed by just specifying in the WHERE clause. Also, Access won't be able to enforce referential integrity for relationships like this one.
answered Nov 14 '18 at 8:26
Erik von AsmuthErik von Asmuth
19.1k62138
edited Nov 14 '18 at 9:13
answered Nov 14 '18 at 8:26
Erik von AsmuthErik von Asmuth
19.1k62138
answered Nov 14 '18 at 8:26
Erik von AsmuthErik von Asmuth
19.1k62138
answered Nov 14 '18 at 8:26
Erik von AsmuthErik von Asmuth
19.1k62138
19.1k62138
Option 3 seems like the best approach. So essentially, one column can hold two foreign keys in the ITEMS table?
– Hoss
Nov 14 '18 at 20:44
Yes, indeed. The second column determines to which column the foreign key refers. In one of my databases, I have a single column that's the foreign key to 6 tables, with a second column specifying which table it refers to. Using 6 columns instead would increase the size of that table significantly
– Erik von Asmuth
Nov 14 '18 at 21:06
add a comment |
Option 3 seems like the best approach. So essentially, one column can hold two foreign keys in the ITEMS table?
– Hoss
Nov 14 '18 at 20:44
Yes, indeed. The second column determines to which column the foreign key refers. In one of my databases, I have a single column that's the foreign key to 6 tables, with a second column specifying which table it refers to. Using 6 columns instead would increase the size of that table significantly
– Erik von Asmuth
Nov 14 '18 at 21:06
Option 3 seems like the best approach. So essentially, one column can hold two foreign keys in the ITEMS table?
– Hoss
Nov 14 '18 at 20:44
Option 3 seems like the best approach. So essentially, one column can hold two foreign keys in the ITEMS table?
– Hoss
Nov 14 '18 at 20:44
Yes, indeed. The second column determines to which column the foreign key refers. In one of my databases, I have a single column that's the foreign key to 6 tables, with a second column specifying which table it refers to. Using 6 columns instead would increase the size of that table significantly
– Erik von Asmuth
Nov 14 '18 at 21:06
Yes, indeed. The second column determines to which column the foreign key refers. In one of my databases, I have a single column that's the foreign key to 6 tables, with a second column specifying which table it refers to. Using 6 columns instead would increase the size of that table significantly
– Erik von Asmuth
Nov 14 '18 at 21:06
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53291520%2fhow-to-design-access-database-table-where-only-one-of-two-fields-will-have-data%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
I don't think there is a way to do this within MS Access. It supports neither check constraints nor triggers.
– Gordon Linoff
Nov 14 '18 at 4:10
2
@Gordon Microsoft Access certainly supports check constraints (when executed in ANSI 92 mode or over an OLEDB connection), and supports data macros which are pretty close to triggers.
– Erik von Asmuth
Nov 14 '18 at 6:30