I want to store many different types of objects in a single table. I
was thinking of using the name value pair approach to achieve this.
Does anybody have any experience with a such a design?
The table might look like this
CREATE TABLE NV (pk int, type int, [name] varchar(100), value
varchar(100))
--Insert a manager - type = 1
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'FirstName', 'John')
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'LastName', 'Smith')
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'Position', 'CEO')
--Insert an employee - type = 2
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'FirstName', 'Joe')
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'LastName', 'Blog')
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'Position', 'Developer')
--Insert an inventory item - type = 3
INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Name', 'Chair')
INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Color', 'White')
INSERT INTO NV (type, [name], val)
VALUES (3, 3, 'Price', '$150')
3  4440  rg*****@polymor phia.com (Ruaidhri) wrote in message news:<76******* *************** ****@posting.go ogle.com>... I want to store many different types of objects in a single table. I
was thinking of using the name value pair approach to achieve this.
Does anybody have any experience with a such a design?
The table might look like this
CREATE TABLE NV (pk int, type int, [name] varchar(100), value
varchar(100))
--Insert a manager - type = 1
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'FirstName', 'John')
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'LastName', 'Smith')
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'Position', 'CEO')
--Insert an employee - type = 2
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'FirstName', 'Joe')
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'LastName', 'Blog')
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'Position', 'Developer')
--Insert an inventory item - type = 3
INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Name', 'Chair')
INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Color', 'White')
INSERT INTO NV (type, [name], val)
VALUES (3, 3, 'Price', '$150')
Generally speaking, this is not good design in a relational database -
each table should represent only one entity. Why not have two tables,
Employees and Inventory? (Managers are employees too.)
In any case, if you can explain what you're trying to do, and why you
are considering this design, then someone may be able to suggest a
better solution.
Simon
The design is for a Biotech company. We were thinking about using
this
design because it is flexible. For example, we can use it to store
materials that we know about now. It can also be used in the future to
store new materials that we don't yet know about yet that might have
different attributes. sq*@hayes.ch (Simon Hayes) wrote in message news:<60******* *************** ****@posting.go ogle.com>... rg*****@polymor phia.com (Ruaidhri) wrote in message news:<76******* *************** ****@posting.go ogle.com>... I want to store many different types of objects in a single table. I
was thinking of using the name value pair approach to achieve this.
Does anybody have any experience with a such a design?
The table might look like this
CREATE TABLE NV (pk int, type int, [name] varchar(100), value
varchar(100))
--Insert a manager - type = 1
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'FirstName', 'John')
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'LastName', 'Smith')
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'Position', 'CEO')
--Insert an employee - type = 2
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'FirstName', 'Joe')
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'LastName', 'Blog')
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'Position', 'Developer')
--Insert an inventory item - type = 3
INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Name', 'Chair')
INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Color', 'White')
INSERT INTO NV (type, [name], val)
VALUES (3, 3, 'Price', '$150')
Generally speaking, this is not good design in a relational database -
each table should represent only one entity. Why not have two tables,
Employees and Inventory? (Managers are employees too.)
In any case, if you can explain what you're trying to do, and why you
are considering this design, then someone may be able to suggest a
better solution.
Simon rg*****@polymor phia.com (Ruaidhri) wrote in message news:<76******* *************** ****@posting.go ogle.com>... The design is for a Biotech company. We were thinking about using
this
design because it is flexible. For example, we can use it to store
materials that we know about now. It can also be used in the future to
store new materials that we don't yet know about yet that might have
different attributes.
<snip>
You might want to have a look at this article from SQL Server Magazine
(June 2003) on super/subtypes: http://www.sqlmag.com/Articles/Index...rticleID=38656
This is one possible solution to the issue of modelling different
items which share some common attributes (Quantity, UnitPrice, etc.),
but have other attributes which only apply to one type of item
(Colour, NumberOfLegs).
Your approach would be extremely difficult to query, index, and
maintain. In fact, taken to an extreme, you would end up with only one
table in the database. Even if you finally decide to have some degree
of denormalization to handle very diverse inventory items, there's no
good reason to include employees in the same table.
Simon This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: brettr |
last post by:
When I reference document.cookie, there is a long string of key=value;
pairs listed. I may have 100 hundred cookies on my hard drive.
However, most only have one key=value pair. Does the document.cookie
variable combine all cookie key=value pairs?
All of the examples I've seen discuss referencing a specific cookie. I
don't see how this is done.
Cookies are usually named by the domain. If I want to reference a
specific cookie, do I...
|
by: Jim Adamson |
last post by:
I have created a web page that receives names and values from a URL
string of another page e.g.
http://hostname/resolve?sublibrary=JMLibrary&collection=Elton&shelfmark=LM
36TY
... and decodes the names/values from the ? onwards, doing all the
seperation of the ampersands.
Here is the "decoder" that I found at
http://www.tek-tips.com/faqs.cfm?fid=5442
|
by: jadamson60 |
last post by:
>From http://developer.irt.org/script/992.htm
=============================================
Q992 How can I accept a name/value pair in the current url and access
it as a variable and value?
If the URL is something like:
http://www.irt.org/index.htm?number=5
|
by: Bush is a Fascist |
last post by:
Hi all,
What do most languages call a name-value pairing?
Or perhaps my question should be, why not just call it
a name-value pairing? Too many syllables?
Did Knuth invent a handy term for such a thing?
Thanks.
|
by: RSH |
last post by:
I need to use a form element to display a list of users. I would like to
store their ID "behind the scenes" so when the user selects a name the Event
passes their ID.
I can't find anything about how to set a Name/Value pair of a ComboList. Is
this the wrong tool for the job? Or how do I set the two values?
Thanks so much,
Ron
| | |
by: David Lozzi |
last post by:
Howdy,
I'm trying to get the values from a string of name/value pairs. I'm using a RegEx (I'm very new to RegEx) expression as seen below
Dim regExp As Regex
Dim m As Match
m = regExp.Match(strResult, "RESULT=((.|\n)*?)&")
|
by: Kevin Blount |
last post by:
I have an issue with trying to add a new name-pair to an existing
cookie. Here's what I want to do:
page1 -
adds "loggedin=true" to cookie "Communities"
adds "member_id=100" to cookie "Communities"
page2 -
adds "banner=1" to cookie "Communities"
|
by: Mohitz |
last post by:
is pair<fine for returning two values?
What about more than two values?
PS: Values may be of different types.
Opinions?
Thank you
Mohit
|
by: Andrus |
last post by:
I'm trying to compile myGeneration PropertyCollectionAll.cs file with VCS
Express 2005 bot got error
Error 1 The type or namespace name 'Collection' could not be found (are you
missing a using directive or an assembly reference?)
PropertyCollectionAll.cs 17 39
I looked to .NET 2 help and found that Collection class is included in
mscorlib so it doen't need assembly reference.
How to fix this error ?
|
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: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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...
| |
