SQLTeam.com | Weblogs | Forums

Design problem


#1

Hi everyone,

I would like to create Db that holds info for cell phones and SIM cards, amoung other things. My question is about designing tables that would hold info of SIMs and Mobile phones. If I use 2 tables I have no elegant solution for purchase table that would hold info on the purchases of both cell phones and SIM cards and Since they both have different attributes that I would like to track, I have no clue what would be good design route to take. Thanks a lot.


#2

Create them a separate tables.

If by "purchase table", you mean a SalesOrder table, that's easy... Typically you'd have an "order header" table that holds the attributes specific to the order itself... and a "Line Item" or "Order Details" table that holds product level information related to the order.

There should be no issue pulling 1 ProductID for "CellPhone" and another from "SimCard"... and another from "OtherProducts"...

If you want to avoid ProductID collisions, you can create a SEQUENCE that all "product" table use to pull IDs from.


#3

Thanks for your help Jason. Since this app would be used to track internal mobile phones usage by users. I guess I would use the same approach to do lendItems table (phone and sim landed to user) using header and line item from two tables for items. Thanks a lot.


#4

Hi there,

I am trying to implement your suggested design and I have a question:

how my purchases table should look like?

If I have

SIMs
'''''''''''''''
SimId
SerialNumber
PhoneNumber

Mobile
'''''''''''''''''''
MobileId
IMEI
SerialNumber

PurchaseHeader
'''''''''''''''''''''''''''''''''''''''
PurchaseId
PurchaseDate

PurchaseLineItem
''''''''''''''''''''''''''''''''''''''''''
PurchaseId
SimId???
MobileId???

How to make PurchaseLineItem table so I can avoid nulls in SimId or MobileId. I have many other attributes, but I have included just a few to illustrate. I was thinking I can do something like this:

PurchaseLineItem
''''''''''''''''''''''''''''''''''''''''''
PurchaseId
ItemId
MobileFlag

and if mobile just make flag true.

Please suggest the best approach.

Thanks a lot.


#5

Two options come to mind... #1 Simply add a "ProdType" column to the LineItems table or #2 use a sequence object to populate the ids for all product tables.

#1 should be easy enough to grasp. Use "ProdType to conditionally join to each of the different product tables. Similar to the following...

SELECT 
	pli.PurchaseId,
	pli.ProductID,
	ProductName = COALESCE(ta.RandomProductName, tb.RandomProductName, tc.RandomProductName),
	pli.Quantity
FROM
	dbo.PurchaseLineItem pli
	LEFT JOIN dbo.Table_A ta
		ON pli.ProductID = ta.ProductID
		AND pli.ProdType = 'A'
	LEFT JOIN dbo.Table_B tb
		ON pli.ProductID = tb.ProductID
		AND pli.ProdType = 'B'
	LEFT JOIN dbo.Table_C tc
		ON pli.ProductID = tc.ProductID
		AND pli.ProdType - 'C';

#2 is just as simple but not many people are familiar how to use a SEQUENCE object to create distributed keys across multiple tables So I'll provide a more robust example...

USE tempdb;
GO

IF OBJECT_ID('tempdb.dbo.ProductSEQ', 'SO') IS NOT NULL DROP SEQUENCE dbo.ProductSEQ;
CREATE SEQUENCE dbo.ProductSEQ
AS INT
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1;


IF OBJECT_ID('tempdb.dbo.Table_A', 'U') IS NOT NULL DROP TABLE dbo.Table_A;
CREATE TABLE dbo.Table_A (ProductID INT NOT NULL PRIMARY KEY, RandomProductName VARCHAR(20) NOT NULL);
IF OBJECT_ID('tempdb.dbo.Table_B', 'U') IS NOT NULL DROP TABLE dbo.Table_B;
CREATE TABLE dbo.Table_B (ProductID INT NOT NULL PRIMARY KEY, RandomProductName VARCHAR(20) NOT NULL);
IF OBJECT_ID('tempdb.dbo.Table_C', 'U') IS NOT NULL DROP TABLE dbo.#Table_C;
CREATE TABLE dbo.Table_C (ProductID INT NOT NULL PRIMARY KEY, RandomProductName VARCHAR(20) NOT NULL);

IF OBJECT_ID('tempdb.dbo.PurchaseLineItem', 'U') IS NOT NULL DROP TABLE dbo.PurchaseLineItem;
CREATE TABLE dbo.PurchaseLineItem (
	PurchaseId INT NOT NULL,
		-- add FK constraint to PurchaseHeader table
	ProductID INT NOT NULL,
	Quantity INT NOT NULL,
	CONSTRAINT pk_PurchaseLineItem PRIMARY KEY (PurchaseId, ProductID)
	);

--============================================================================================

INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-1');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-1');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-1');

INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-2');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-2');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-2');

INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-3');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-3');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-3');

INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-4');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-4');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-4');

INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-5');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-5');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-5');

INSERT dbo.PurchaseLineItem (PurchaseId, ProductID, Quantity) VALUES 
	(123455, 1, 3), (123456, 2, 1), (112233, 3, 1), (32123, 4, 1),
	(123455, 5, 3), (123456, 6, 1), (112233, 7, 1), (32123, 8, 1),
	(123455, 8, 3), (123456, 10, 1), (112233, 11, 1), (32123, 12, 1);

SELECT * FROM dbo.Table_A ta;
SELECT * FROM dbo.Table_B tb;
SELECT * FROM dbo.Table_C tc;

--============================================================================================

SELECT 
	pli.PurchaseId,
	pli.ProductID,
	ProductName = COALESCE(ta.RandomProductName, tb.RandomProductName, tc.RandomProductName),
	pli.Quantity
FROM
	dbo.PurchaseLineItem pli
	LEFT JOIN dbo.Table_A ta
		ON pli.ProductID = ta.ProductID
	LEFT JOIN dbo.Table_B tb
		ON pli.ProductID = tb.ProductID
	LEFT JOIN dbo.Table_C tc
		ON pli.ProductID = tc.ProductID;