CODEWITHSUNDEEP
sqldatabasems-accessdatabase-design
Hoss
Hoss

Reputation: 23

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?

Upvotes: 0

Views: 369

Answers (1)

Erik A
Erik A

Reputation: 32682

In essence, there are 3 things you can do:

  1. 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.

  2. 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

  3. 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.

Upvotes: 2

Related Questions