December 1, 2003 at 11:08 am
Folks,
I am trying to devise a set of tables to keep orderly some "items" that might come from two independent sources. Depending on the sources, we need to track different information about them. I would prefer a nice, tight table structure rather than one where we have lots of NULLs.
Let's pretend we're talking about a car dealer who sells cars from two sources: deliveries from the factory, and self-made cars made by his son. Weird, I know, but it works for my purposes. Assume that while both types of cars have a "color", the paint for the self-made cars is also made by the son, so he needs to keep track of what paint-formula he used (so he can do repairs, replace panels, etc.).
The existing table structure (ugly and NULL-rich) is like so...
CREATE TABLE dbo.Dealers_Cars
(
CarID int NOT NULL IDENTITY (1, 1), -- unique id for each
Color char(10) NOT NULL, -- generic color (e.g., "blue")
Doors tinyint NOT NULL, -- number of doors
FactoryLotID int NULL, -- One number for all factory cars in a single delivery
FactoryDeliveryDate smalldatetime NULL, -- delivery date for factory-made cars
FactoryModelNumber char(10) NULL, -- factory's model for each car
SelfMadeDate smalldatetime NULL, -- date car was built
SelfMadePaintForumlaCode char(10) NULL, -- builder's custom paint code
SelfMadeDescription varchar(50) NULL -- builder's own description of car
)
The "Factory*" fields will be NULL for a self-made car. The "SelfMade*" fields will be NULL for factory cars. Neither set of fields can be cleanly mapped to the other (we'll say FactoryDeliveryDate and SelfMadeDate are distinct enough that they can't share a single common field).
How would you structure your tables?
December 1, 2003 at 12:15 pm
Your factory cars and your custom cars could both use model references - you just have to create a model/manufacturer
for the custom cars also. Something like Manufacturers->Models->Cars, then Purchases->Deliveries->Sales }> Inventory? (i'm clueless about cars/models/doors/etc myself).
Looking at it in a smaller scope:
CREATE TABLE dbo.Cars
(
CarID int NOT NULL IDENTITY (1, 1) primary key, -- unique id for each
Color char(10) NOT NULL, -- generic color (e.g., "blue")
Doors tinyint NOT NULL, -- number of doors
)
CREATE TABLE dbo.FactoryCarData
(
CarID int NOT NULL primary key, -- FK
FactoryLotID int not null, -- One number for all factory cars in a single delivery
FactoryDeliveryDate smalldatetime not null, -- delivery date for factory-made cars
FactoryModelNumber char(10) not null, -- factory's model for each car
)
create table dbo.CustomCarData
(
CarID int NOT NULL primary key, -- FK
SelfMadeDate smalldatetime not null, -- date car was built
SelfMadePaintForumlaCode char(10) not null, -- builder's custom paint code
SelfMadeDescription varchar(50) not NULL -- builder's own description of car
)
create view dbo.vCars as
select
a.CarID
,a.Color
,a.Doors
,b.FactoryLotID
,b.FactoryDeliveryDate
,b.FactoryModelNumber
,c.SelfMadeDate
,c.SelfMadePaintFormulaCode
,c.SelfMadeDescription
from
Cars as a
left outer join FactoryCarData as b a.CarID = b.CarID
left outer join CustomCarData as c on a.CarID = c.CarID
or set up a function/check to ensure that data only exists in one of the sub-tables for the car data (jonathan posted a how-to, but don't know if he agrees with my post here), then coalesce the similar fields together and give them a standard name, parsing nulls out with isnull() for those that cant be generalized.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply