name value pair design

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')rgarvey@.polymorphia.com (Ruaidhri) wrote in message news:<7681c3eb.0402111626.6173889b@.posting.google.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.

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0402120128.7fdad08d@.posting.google.com>...
> rgarvey@.polymorphia.com (Ruaidhri) wrote in message news:<7681c3eb.0402111626.6173889b@.posting.google.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|||rgarvey@.polymorphia.com (Ruaidhri) wrote in message news:<7681c3eb.0402121628.70a8488f@.posting.google.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/Inde...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.

Simonsql

mysql EXPLAIN = what in MS SQL 2000

If you have a table namedUsers then using mysql
you can runEXPLAIN Users; to get all the fields & types, etc.
How would you do this same thing in SQL 2000?There are several ways to get this information. sp_help would be a good place to start: sp_help Users

Terri

My SSIS problems text file, date data types

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging.

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated.
Thanks,The fact I got problem no.2 makes me look stupid - and I don't like to look stupid - so therefore I blame the thing which made me look stupid = MS.

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

Perhaps another way to look at this is in the context of "reasonable defaults." I would not expect the default import/export size to be 40 M rows, so I personally do not think that the package created by default should need to handle this data volume without some sort of review or modification.

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

I certainly cannot argue with your experience, but for me the opposite was true. For me, DTS was practically impossible to work with for anything but the most trivial uses. My standard line when speaking on BI topics is that would prefer to chew off my own mouse finger rather than work with DTS on a project. SSIS certainly has its faults (and upgeading DTS packages is definitely not one of its strengths), but for data warehousing and other real-world ETL purposes it is light-years ahead of DTS.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

That is weird. I just checked one of mine and I can edit most fields. Especially the category drop down, which is what needs to be changed in this case. I can't offer any help on that, I guess.|||Awesome feedback (and at least I am not the only one who experiences technical difficulties with this product ) - thanks guys.

The only improvement over DTS I see is the error handling/logging mechanism. Since working with SSIS close to a year now - I've trimmed down my toolbox to just "Execute SQL", "Script Task" and Very Simple data-flow.

One thing I've spent a lot of time on though is setting up the proper data types for text file (I've already moaned about how cumbersome that is - there should be a way to input your format file instead of typing & mouse selecting) - but by looking that SSIS can't handle datetime + it is difficult to correct production SSIS package when text file field changes (with the amount of files we process it happens quite often - few times a month perhaps) - our consultant suggested to import all data as string to database - and then use SQL to do conversions. In that case when a file changes - you only need to modify your SQL (views)

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.

My SSIS problems text file, date data types

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging.

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated.
Thanks,The fact I got problem no.2 makes me look stupid - and I don't like to look stupid - so therefore I blame the thing which made me look stupid = MS.

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

Perhaps another way to look at this is in the context of "reasonable defaults." I would not expect the default import/export size to be 40 M rows, so I personally do not think that the package created by default should need to handle this data volume without some sort of review or modification.

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

I certainly cannot argue with your experience, but for me the opposite was true. For me, DTS was practically impossible to work with for anything but the most trivial uses. My standard line when speaking on BI topics is that would prefer to chew off my own mouse finger rather than work with DTS on a project. SSIS certainly has its faults (and upgeading DTS packages is definitely not one of its strengths), but for data warehousing and other real-world ETL purposes it is light-years ahead of DTS.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

That is weird. I just checked one of mine and I can edit most fields. Especially the category drop down, which is what needs to be changed in this case. I can't offer any help on that, I guess.|||Awesome feedback (and at least I am not the only one who experiences technical difficulties with this product ) - thanks guys.

The only improvement over DTS I see is the error handling/logging mechanism. Since working with SSIS close to a year now - I've trimmed down my toolbox to just "Execute SQL", "Script Task" and Very Simple data-flow.

One thing I've spent a lot of time on though is setting up the proper data types for text file (I've already moaned about how cumbersome that is - there should be a way to input your format file instead of typing & mouse selecting) - but by looking that SSIS can't handle datetime + it is difficult to correct production SSIS package when text file field changes (with the amount of files we process it happens quite often - few times a month perhaps) - our consultant suggested to import all data as string to database - and then use SQL to do conversions. In that case when a file changes - you only need to modify your SQL (views)

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.