November 13, 2009 at 10:03 am
I have a flat table with the following columns:
CREATE TABLE [dbo].[Trucks](
[Company] [nvarchar](50) NOT NULL,
[License] [nvarchar](20) NULL,
[Year] [float] NOT NULL,
[Make] [nvarchar](20) NOT NULL,
[Model] [nvarchar](20) NOT NULL,
[Location] [nvarchar](50) NULL,
[UnitNumber] [float] NOT NULL,
[PrimaryAir] [nvarchar](30) NULL,
[PrimaryAirQty] [float] NULL,
[SecondaryAir] [nvarchar](30) NULL,
[SecondaryAirQty] [float] NULL,
[PrimaryOil] [nvarchar](30) NULL,
[PrimaryOilQty] [float] NULL,
[SecondaryOil] [nvarchar](30) NULL,
[SecondaryOilQty] [float] NULL,
[PrimaryFuel] [nvarchar](30) NULL,
[PrimaryFuelQty] [float] NULL,
[SecondaryFuel] [nvarchar](30) NULL,
[SecondaryFuelQty] [float] NULL,
[Coolant] [nvarchar](30) NULL,
[CoolantQty] [float] NULL,
[Hydraulic] [nvarchar](30) NULL,
[HydraulicQty] [float] NULL
And I am thinking that I should have a table called "Filter Kits". Essentially, every truck with a given year make and model will use the same filters, basically a filter kit. I have about 700 rows/trucks in the table now, and I probably have about 40 "filter kits".
My questions are, would creating a "Filter Kits" table make sense? And if so, how do you go about moving the data to the new table? I've found loads of info on normalization, but very little help with how to actually split the table.
Thanks guys
November 13, 2009 at 10:22 am
Hi
This table appears to reflect a whole database. I have no real clue about your business and trucks 😛 but there are some tables I see:
* Companies: Id, Name
* Makes: Id, Name
* Models: Id, MakeId, Name
* Locations: Id, Name
* Airs: Id, Name
* Oils: Id, Name
* Fuels: Id, Name
* Coolers: Id, Name
* Hydraulics: Id, Name
* Truck: Id, ModelId, LoctionId, CoolantId, CoolantQty, HydraulicId, HydraulicQty
* TruckAirs: TruckId, AirId, Quantity
* TruckOils: TruckId, OilId, Quantity
* TruckFuels: TruckId: FuelId, Quantity
Disclaimer: As already said, I have no clue about your business.
Greets
Flo
Edit: Forgot the quantities
November 13, 2009 at 12:49 pm
Thanks Flo,
Your ideas were actually much more detailed than I was thinking, but I can see the reason for doing it like you showed.
The one bottleneck I still have is how to actually separate all that data out. When I try to create a separate "Companies" table for instance, the only way I know to do that is to create a new table, populate it with the 6 company names we have in the current table, then go back to the original table and add a "CompanyID" column and manually insert the correct ID value for each truck. Time consuming, but not impossible for me - only 491 rows. But I'm sure there is a better method.
Thanks again.
November 13, 2009 at 1:13 pm
Hi
What you have to do is to define the new tables and add the columns to your trucks table. The data migration can be done automatically.
Here is a little sample how to move the company information from trucks table to a companies table and link them back into the trucks table:
-- sample trucks table
DECLARE @trucks TABLE
(
Id INT NOT NULL IDENTITY(1,1)
PRIMARY KEY CLUSTERED
,Company NVARCHAR(50)
,CompanyId INT
);
-- sample companies table
DECLARE @companies TABLE
(
Id INT NOT NULL IDENTITY(1,1)
PRIMARY KEY CLUSTERED
,Name NVARCHAR(50)
);
-- some sample data into trucks table
INSERT INTO @trucks (Company)
VALUES ('Comp1')
,('Comp2')
,('Comp2');
-- move companies into own table
INSERT INTO @companies (Name)
SELECT DISTINCT
Company
FROM @trucks
-- link companies to trucks
UPDATE t SET t.CompanyId = c.Id
FROM @trucks t
JOIN @companies c ON t.Company = c.Name
-- show result
SELECT * FROM @companies;
SELECT * FROM @trucks;
Greets
Flo
November 13, 2009 at 3:55 pm
Awsome, thanks Flo!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply