One table or three

[Max Moor]

Hi All,
I have a table I use to store information about items I'll sell.
This table has about 20 fields. I also sell just tickets sometimes. I
only need 4 fields to store all the info I need on these. These four
fields are in the big table. What I wonder is if I should split these into
a separate table, or leave them in the original, large table?
One thing I've thought of is that getting a report on total sales
would be easier to query if they were all in the same table. I worry about
wasting a bunch of space having 20 fireld records store 4 field pieces of
daya, though. (Even though the unused fields would be 0 or null.)
I know I've been fairly general here, but can anyone give me any
advice on how I should decide what to do?

- Max

 

[Michel Walsh]

Hi,


All depends. If you have core data and optional data, it may become easier
to write validations rules with one-to-one tables. As example, assume a
table of employees. Some, but not all, take a life insurance and if they do
so, then there must be a not null value under the field "Beneficiary".


Employees
EmployeeID , ...




LifeInsurance
EmployeeID, Beneficiary, Premium, ...



With that design, make a one-to-one relation from Employees to LifeInsurance
(an employee cannot take two, or more, life insurance) and oblige
Beneficiary, and Premium, to be NOT NULL. In addition, if an employee take a
life insurance, then a record is added in the table, and filled. If not, the
employee is not in the table LifeInsurance, that's all, since no data is
required.


To get the "whole" picture, a simple outer join work fine:


SELECT ...
FROM Employees LEFT JOIN LifeInsurance ON
Employees.EmployeeID=LifeInsurance.EmployeeID


If you have just one table, making a table validation rule about the Premium
cannot be null if there is a Beneficiary is much more complex to formulate,
and to maintain. That is where a one-to-one relation may become usefull.



Hoping it may help,
Vanderghast, Access MVP