Unknown additional fields...
-
Hello guys,
I'm trying to design a Line Of Business database app for ourselves as a AV subcontractor that works at many sites on behalf of a few different clients. Occasionally the same sites for different clients!
I have a table that details site information (for the service call industry). It contains bog standard fields like Address, phone number, email, etc.
I need to add various, inconsistant items of information, of varying datatypes to the site. (eg number of screens, notes on an installation). I do not, and cannot know all the various additional field types to start with.
Is it possible to create an 'extensible' table?
These items are also client specific (I have a clients table also), and in the future the difffent clients may interact with the database with their own view.
My first solution seems a bit horrific, two table tables, one to define the data/'column' name as a lookup, and another linked by key to hold string variables (which also has a FK to link to clients)
I am more than aware that this approach is not something I actually want to do! But as a novice, and occasional programmer, I am not aware of a better solution.
I'd be greatful for any help, or change of perspective!
-
As a novice, you already have one foot dangling over the precipice! Building 'on the fly' systems is extremely complex and certainly not for the faint of heart. Better would be to build in additional fields that are deliberately left blank, but can assume definition as the need arises. Or simply add new fields as the need arises, to the appropriate tables.
The secret to good data design here lies in a rich understanding of the business you are trying to support. If worse comes to worse, you could build a 'generic' table with the following fields:
PK
FK (pointing to the client OR the facility or both, depending on specific use)
a text field to hold the description of the content
a type field to indicate the data type and
a field of each definition type.
You only populate the appropriate column for a given row. Leaves you with lots of 'empty' space in a grid display of the table, but gives you the flexibility you need in the db design.
Periodically, you will need to review this 'catch-all' table to determine when you can move content out of the catch-all and place it in a 'regular' table.
I smell job security....
-
Steve, To build on what you started, the data field in your 'generic table' could also be a SQL VARIANT, then you would only need one column for the data instead of defining a column of each data type.
Yes, I smell job security also... (not a bad thing either!)
-
Thanks for you suggestions, they really confirm what I was going to do! I was hoping for an easier way out tho
On the subject of job security, my boss has already stated he won't fire me, ever, lol.
-
Another option would be to put the "extra" fields in a separate table linked by the key in the first table. That way you could have as many new pieces of information as you need without having to change the structure of the original table.
I would put a description field and a data field in the dependent table. Making the data field a SQL variant field would provide you with the ability to put any kind of data in the field.
Just a thought,
-
I am expressing a personal bias here, based in part on my not having to use a variant field: It's only a small step from variant to deviant. And deviations bring on nightmares.
-
In spite of what I stated in my last message, I agree with you.
I don't believe I have a single variant field anywhere in my databases. I still think the best solution would be to move the "extra" fields to a separate table, regardless of what kind of field it is.
-
I guess I'm missing something here but looking at the original post he states that he's trying to add fields and gives 2 examples. Both examples look like data elements to me not fields. Number of screens would be a numeric field in the site table and Notes would be a memo field related to both the site and the client.
-
Another option in design use meta data style this is how it works
------------------------------------------------------------------
-- script created by Scott Tolstoy to show sample of Meta-data design table
-- with Pivot table view in SQL Server 2005
-- warning try to Pivot view see more comments in end of sql script
--==========================================================================
-- table that stored specified client
----------------------------------------------------------------------------
CREATE TABLE dbo.Client ( Client_ID INT IDENTITY (1,1)
, Client_Name VARCHAR(32)
, Client_Address VARCHAR(100)
--, ...
)
GO
----------------------------------------------------------------------------
ALTER TABLE dbo.Client
ADD CONSTRAINT PK_Client
PRIMARY KEY CLUSTERED (Client_ID)
GO
-----------------------------------------------------------------------------
-- table that stored different Missolenius Information
-----------------------------------------------------------------------------
CREATE TABLE dbo.Missolenius(Missolenius_ID INT IDENTITY (1,1)
,Missolenius_Column_Name VARCHAR(50)
,Missolenius_Data_Type VARCHAR(20)
)
GO
----------------------------------------------------------------------------
ALTER TABLE dbo.Missolenius
ADD CONSTRAINT PK_Missolenius
PRIMARY KEY CLUSTERED (Missolenius_ID)
GO
----------------------------------------------------------------------------
-- "fork" table between specific Client and different Client Information
----------------------------------------------------------------------------
CREATE TABLE dbo.Client_Missolenius (Client_ID INT NOT NULL
,Missolenius_ID INT NOT NULL
,Client_Missolenius_Value VARCHAR(8000)
)
GO
----------------------------------------------------------------------------
ALTER TABLE dbo.Client_Missolenius
ADD CONSTRAINTPK_Client_Missolenius
PRIMARY KEY CLUSTERED
(Client_ID
, Missolenius_ID
)
GO
----------------------------------------------------------------------------
-- creating foreign keis
----------------------------------------------------------------------------
ALTER TABLE dbo.Client_Missolenius
ADD CONSTRAINTFK_Client_Missolenius_Client
FOREIGN KEY(Client_ID)
REFERENCES dbo.Client (Client_ID)
GO
----------------------------------------------------------------------------
ALTER TABLE dbo.Client_Missolenius
ADD CONSTRAINTFK_Client_Missolenius_Missolenius
FOREIGN KEY(Missolenius_ID)
REFERENCES dbo.Missolenius(Missolenius_ID)
GO
--====================================================================
--Insert Test Data
----------------------------------------------------------------------
INSERT INTO Client (Client_Name)
VALUES ('mr.Smith')
INSERT INTO Client (Client_Name)
VALUES ('McDonald Inc.')
GO
INSERT INTO Missolenius (Missolenius_Column_Name,Missolenius_Data_Type)
VALUES ('Is_Person','bit')
INSERT INTO Missolenius (Missolenius_Column_Name,Missolenius_Data_Type)
VALUES ('Is_Pets_In_House','bit')
INSERT INTO Missolenius (Missolenius_Column_Name,Missolenius_Data_Type)
VALUES ('Insurance Name','VARCHAR(50)')
INSERT INTO Missolenius (Missolenius_Column_Name,Missolenius_Data_Type)
VALUES ('Number_Of_Employee','Int')
INSERT INTO Missolenius (Missolenius_Column_Name,Missolenius_Data_Type)
VALUES ('Missolenius','VARCHAR(1000)')
GO
INSERT INTO Client_Missolenius (Client_ID,Missolenius_ID,Client_Missolenius_Value)
VALUES (1,1,'1')
INSERT INTO Client_Missolenius (Client_ID,Missolenius_ID,Client_Missolenius_Value)
VALUES (1,2,'1')
INSERT INTO Client_Missolenius (Client_ID,Missolenius_ID,Client_Missolenius_Value)
VALUES (1,3,'Farmers Insurance')
INSERT INTO Client_Missolenius (Client_ID,Missolenius_ID,Client_Missolenius_Value)
VALUES (2,1,'0')
INSERT INTO Client_Missolenius (Client_ID,Missolenius_ID,Client_Missolenius_Value)
VALUES (2,3,'Some chip Insurance
')INSERT INTO Client_Missolenius (Client_ID,Missolenius_ID,Client_Missolenius_Value)
VALUES (2,4,'1234567')
INSERT INTO Client_Missolenius (Client_ID,Missolenius_ID,Client_Missolenius_Value)
VALUES (2,4,'1234567')
GO
--=========================================================================
-- Create Views
---------------------------------------------------------------------------
CREATE VIEW vw_Client_Missolenius
AS
select c.Client_ID
, Client_Name
, Client_Address
, m.Missolenius_ID
, Missolenius_Column_Name
, Client_Missolenius_Value
, Missolenius_Data_Type
from Client c
JOIN Client_Missolenius cm
ON c.Client_ID=cm.Client_ID
JOIN Missolenius m
ON cm.Missolenius_ID=m.Missolenius_ID
---------------------------------------------------------------------------
-- create pivot view
---------------------------------------------------------------------------
CREATE VIEW vw_Pivot_Client_Missolenius
AS
SELECT Client_ID
,CONVERT(bit,[1]) AS 'Is_Person'
,CONVERT(bit,[2]) AS 'Is_Pets_In_House'
,CONVERT(VARCHAR(50),[3]) AS 'Insurance Name'
,CONVERT(int,[4]) AS 'Number_Of_Employee'
,CONVERT(VARCHAR(1000),[5]) AS 'Missolenius'
FROM
(SELECT Client_ID, Missolenius_ID, Client_Missolenius_Value
FROM Client_Missolenius) s
PIVOT
(
MAX(Client_Missolenius_Value)
FOR Missolenius_ID IN ([1],[2],[3],[4],[5])
) p
--ORDER BY [Y]
GO
--====================================================================
select * from vw_Client_Missolenius
select * from vw_Pivot_Client_Missolenius
-- -------------------------------------------------------------------
-- PIVOT VIEW can be created dynamical so every time when your
-- "fork" table modified PIVOT VIEW is recreated.
-- P.S. I recommend to avoid PIVOT VIEWS but some times you don't have
-- a choice, when you redesigning(normalizing) database and one of tables
-- has more then 100 columns. To Simulate old structure of table
-- create meta-data tables and PIVOT VIEW
----------------------------------------------------------------------
-
I've done the variant thing and it was problematic for queries at times. The implicit casting in your queries sometimes causes issues when to tries to bang dates into ints.
I'd vote for a vertically partitioned table. That way you don't have to access it if the data isn't needed or isn't often populated. Unless this is millions of rows, it's prob not a perf issue.
So I'd do
create MainTable
( PK int (or whatever)
, Name
, address
, phone
...
)
create table MainTableSparse
( PK int
, spareseint1 int
, sparsechar1 varchar
..
)
Then if you need to check for data or get it from the sparse fields, you can. I'd definitely try to name the sparse fields for their purpose if you know. I hate seeing Int1, Int2, Int3 fields.
-
Well this is classic situation, how you normalize relationship between tables that have many to many relationship. You need to have 3rd table "fork" one.
-
to make PIVOT table in SQL 2005 it's needs got against one column that is why it's there.
If you don't need pivot View on the end then
the structure of Client_Missolenius will be
CREATE TABLE dbo.Client_Missolenius (Client_ID INT NOT NULL
,Missolenius_ID INT NOT NULL
,Client_Missolenius_Bit bit NULL
,Client_Missolenius_Int int NULL
,Client_Missolenius_Varchar Varchar(50) NULL
,Client_Missolenius_Date datetime NULL
)
--------------------------------------------------------------------------
it's nice to know all columns and data types upfront, but each company, business rules and databases (exists ones) is different.
-
from original post
---------------------------------------------------------------------------------
"I have a table that details site information (for the service call industry). It contains bog standard fields like Address, phone number, email, etc.
I need to add various, inconsistant items of information, of varying datatypes to the site. (eg number of screens, notes on an installation). I do not, and cannot know all the various additional field types to start with."
---------------------------------------------------------------------------------
Based on this information I created example with Client and Missolanious where one Client can have many Missolanious items and one Missolanious item can belongs to many Clients.
that is many to many relationship.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply