Greg Nudelman wrote:
> Design DB tables from the following Object scheme:
>
> CustomersAbstractSuperclass
> ----------------
> id
> name
> address
> ----------------
> ----------------
>
> inherited individualy by:
>
> Customer
> ----------------
> favorites
> comments
> ----------------
> ----------------
>
> and:
>
> WholesaleCustomer
> ----------------
> limit
> pin
> department
> ----------------
> ----------------
>
>
> +++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> I proposed a single table:
>
> Customers
> ----------------
> id
> name
> address
> favorites
> comments
> limit
> pin
> department
> objectType
>
No good. I assume the objectType field indicates if the record is that
of a WholesaleCustomer or Customer. That means there are some fields
that depend on something other than the primary key. This violates
second normal form (2nf). Right there, you should have known you needed
at least a second table. Keep everything that depends on the primary
key (id) and ONLY the primary key in the first table and move everything
else to a second table. Thus:
Table1:
-------
id
objectType
name
address
Table2
-------
id
favorites
comments
limit
pin
department
As you can plainly see, Table2 has two independent sets of fields only
one of which is dependent on the key at a time. So break it into two
separate tables.
Table2A (Customer)
---------
id
favorites
comments
Table2B (WholesaleCustomer)
--------
id
limit
pin
department
So now all fields in each table are related to the key of each table and
the fields have no dependencies with each other. If you wanted a list
of all customers, you would select against Table1. If you wanted a list
of generic (Retail?) customers, you would join Table1 with Table2A. If
you wanted a list of Wholesale customers, you would join Table1 with
Table2B.
You could also suggest two views, one for each join, to simplify working
with this group of tables.
> to which Mr. interviewer replied: this will waste space. As
> WholesaleCustomer record will have Customer fields empty (and vice
> versa). Also, you will be required to search one giant table by
> specific objectType to get customers or wholesale customers:
>
> SELECT * FROM Customers WHERE objectType=1
>
Another problem is that this statement will return a result set with
fields that are not applicable. If objectType=1 represents
WholesaleCustomer, the result set will still contain a favorites and
comments columns. Assuming the fields would contain null values, they
still should not be there at all.
> I said OK, alternatively (WORSE) you can also split this in 2 tables,
> Customer and WholesaleCustomer.
>
> However, if you often have to run queries against the whole set,
> you'll end up with lots of redundant app logic or SQL joins/unions of
> 2 tables to do composite sorts, paging through the whole collection,
> and getting totals/averages from 2 types of objects.
>
True. The name and address fields are common so should be in one table.
> He proposed a third scheme then.
>
> 1) Can you guess what it was (hint: he used 3 tables)?
>
My guess is above.
> 2) Would this third scheme be better then my single table? Why?
>
Yes. As all information common to all customer resides in one table,
you can retrieve that information with a single select statement when
you want to do something that uses all customers. The information that
is dependent on the type of customer is located in separate tables.
This is beneficial in two ways:
1. If you are working with one type of customer, you can retrieve all
applicable information (and no non applicable information) with a single
select statement.
select t1.id, name, address, favorites, comments
from table1 t1, table2a t2
where t1.objectType=0 and t1.id = t2.id;
or
select t1.id, name, address, limit, pin, department
from table1 t1, table2b t2
where t1.objectType=1 and t1.id = t2.id;
2. The arrangement is scalable. Should there ever be a requirement for
a third type of customer with a different set of data, simply add
another table with the appropriate fields. No modifications to the
existing tables will be needed and existing applications will not break.
Tomm
To change your membership options, refer to:
http://www.sys-con.com/java/list.cfm
