I have a client who wants to build a database with a user extensible
schema.
The client wants to model an entity with about 100 numeric attributes.
As time goes on, more numeric attributes will be added. The client
wants an admin function that will allow him to add these attributes
when needed.
My instinct is that this is wrong and that bad things will happen if
we build this. However, when I went to write up the reasons, all I
could come up with was that
* we would need to write dynamic SQL to handle the changing table
structure,
* that table locking would result from field additions
* and that it was "non-standard"
One alternative is to create a table containing field-name/value
pairs. This is ugly and breaks the ER modelling rules that tell me yo
put attributes of an item into the same row. All the same, I would
prefer this method to the first approach.
This problem does not arise on database projects that follow the
pattern of only allowing DDL commands to be issued during the initial
development phase and during subsequent change requests.
Have any of you been faced with a similar problem? What should I do?
Karen 4 1457
Karen Sundquist (ka********@yah oo.com) writes: One alternative is to create a table containing field-name/value pairs. This is ugly and breaks the ER modelling rules that tell me yo put attributes of an item into the same row. All the same, I would prefer this method to the first approach.
This would be my preference too.
Of course, neither this is going to be fun if you want reports with
these attributes as columns. You could define views that twisted
the rows as columns, but with over 100 columns that will be a horrible
self-join if all attributes are to be included.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Actually, the grotesque-looking aggregation of the attribute/value table
is reasonably fast.
It also gives you a way to enforce TYPES on the values,
which is another real issue with attribute/value format.
The underlying table is still amazingly spacewasteful,
but if you HAVE to ....
CREATE VIEW Normal AS
SELECT key1, key2, ...
min(case when attribute = 'name' then value end) as name,
min(case when attribute = 'asof' then cast(value as
datetime) end) as asof,
...
FROM Abnormal
GROUP BY key1, key2, ...
I can't speak for the MSSQL engine, but proper dragging and beating
in the query tree ought to eliminate any processing to support
unused columns (MIN(...) expressions) when making reference
to the view.
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.0.0.1... Karen Sundquist (ka********@yah oo.com) writes: One alternative is to create a table containing field-name/value pairs. This is ugly and breaks the ER modelling rules that tell me yo put attributes of an item into the same row. All the same, I would prefer this method to the first approach.
This would be my preference too.
Of course, neither this is going to be fun if you want reports with these attributes as columns. You could define views that twisted the rows as columns, but with over 100 columns that will be a horrible self-join if all attributes are to be included.
-- Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Mischa and Erland, thanks for your advice. I am going to go with the
field/value pairs, as I did the last time I had this problem.
Karen
Mischa Sandberg (mi************ *@telus.net) writes: It also gives you a way to enforce TYPES on the values, which is another real issue with attribute/value format.
Apparently in Karen's case, the attributes were numeric, so maybe it's
issue this time.
In the general case where attributes are of mixed types, you can use the
the sql_variant datatype, and supplement with a column which defines
the datatype. Then you have a constraint which goes like this:
sql_variant_pro perty(value, 'Basetype') =
CASE typeofdata
WHEN 'B' THEN bit
WHEN 'D' THEN datetime
...
END
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mark Wilson CPU |
last post by:
A colleague has written a prototype program in PHP, using a MySQL
database.
It's a relatively simple app, with a restricted set of mysql commands
used (see below). The MySQL DB is being replaced with an Oracle DB
(same schema). My plan
1) globally replace the few mysql commands with intermediate
equivalents (such as myDB_connect for mysql_connect)
2) those central functions would then (for now) call the original
mysql function to prove...
|
by: Fran Tirimo |
last post by:
I am developing a small website using ASP scripts to format data retrieved
from an Access database. It will run on a Windows 2003 server supporting
FrontPage extensions 2002 hosted by the company 1&1 with only limited server
configuration via a web based control panel.
My query relates to the ASP security model and how it relates to FrontPage
options for setting file access on a database file. If you know of any
online documentation...
|
by: Florian von Savigny |
last post by:
Hi there,
I am having a hard time in grasping how the TBX (TermBase eXchange
format), authored by LISA (http://www.lisa.org/tbx/) exactly
works. What I seem to have understood is the following:
- although TBX *is* defined in terms of a DTD, it does not define it
in full; it is more like it defines a general structure that can be
|
by: Mike |
last post by:
Is there a way to determine what a user's default email client is? I
read a post from 3 years ago that said no. I guess I'm hoping
something has come along since then.
|
by: Stefan Mueller |
last post by:
I choose 'Entry 4' and click then on the button 'Set' to set the index to
'Entry 2'. If you press now the cursor down key 'Entry 5' instead of 'Entry
3' shows up with Mozilla Firefox. With Internet Explorer and Opera it works
fine; 'Entry 3' shows up.
Is this a bug within Mozilla Firefox or do I have any possibility to change
this wrong behavior?
Stefan
========================
| |
by: d0wsdkn02 |
last post by:
I have an ASP.NET application that performs ADO.NET commands that cause
triggers to be run in SQL Server 2000.
My question is how would I best access the user ID from my custom .NET
Principal in the trigger (the trigger stores audit information and
needs the user ID)?
I'm using the same SQL Server user/pass on every connection to
facilitate connection pooling, so it's not available that way.
|
by: xeroxero |
last post by:
I would like to prevent people from downloading a .zip from a ASP.NET
2.0 web site, but still allow people to touch a .aspx page in the same
directory. I also want to set things so if a user clicks on a button,
the ASP.NET page will facilitate a download with Response.BinaryWrite.
Is that possible?
Thanks.
|
by: Nosferatum |
last post by:
This script is meant to limit access by sessions, using username and
password from mysql db and redirect users after login according to a
given value belonging to each user in the db (10,20,30,40).
(the included config is just server settings, the login is just a
login form).
The script appear to connect but will not redirect users, it seems
that even with correct login details, it won't validate.
|
by: Rahul B |
last post by:
Hi,
I have a user UCLDEV1 which is a part of staff and a
group(db2schemagrp1) to which i have not given any permissions.
The authorizations of that user are shown as
db2 =get authorizations
Administrative Authorizations for Current User
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |