name value pair design

[Ruaidhri]

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')

[Simon Hayes]

Re: name value pair design

rgarvey@polymor phia.com (Ruaidhri) wrote in message news:<7681c3eb. 0402111626.6173 889b@posting.go ogle.com>...[color=blue]
> 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')[/color]

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

[Ruaidhri]

Re: name value pair design

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.



sql@hayes.ch (Simon Hayes) wrote in message news:<60cd0137. 0402120128.7fda d08d@posting.go ogle.com>...[color=blue]
> rgarvey@polymor phia.com (Ruaidhri) wrote in message news:<7681c3eb. 0402111626.6173 889b@posting.go ogle.com>...[color=green]
> > 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')[/color]
>
> 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[/color]

[Simon Hayes]

Re: name value pair design

rgarvey@polymor phia.com (Ruaidhri) wrote in message news:<7681c3eb. 0402121628.70a8 488f@posting.go ogle.com>...[color=blue]
> 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.
>[/color]

<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.cfm?ArticleID=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