Null values or multiple tables

I have a table that stores SNMP Mib values.
These values can be either numeric or text.
I would like to store the text values in a varchar column and the
numeric values in a double column. This of course would help
performance amongs other things.
The question is this:
Is it better to have two tables, one that stores the numeric values
and one that stores the text values.
ex)
CREATE TABLE [dbo].[SnmpMibDataText] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[SnmpMibDataNumeric] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [float] NOT NULL
) ON [PRIMARY]
Or is it better to have 1 table with two columns, one for text and one
for numeric?
CREATE TABLE [dbo].[SnmpMibData] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValueText] [nvarchar] (255),
[SampleValueNumeric] [float]
) ON [PRIMARY]
Now when the Mib value is numeric the text column would be null, and
when the mib value is text the numeric column would be null.
When i ask what is better, i mean overall better (performance,
usability, extendability, etc)?
Thanks in advance
MarkThe one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
--
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.com...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark|||I would probably go with a three table design: a master table that had the
common definitions and, then, two 1-to1 or 1-to-many child related tables to
store the partiallly populated information. NULL-valued columns suit some
purposes but not usually the condidtion where you just don't know the
information or it is not applicable for some of the rows. That indicates
that it is not an attribute of the parent entitiy but subordinate entity
altogether.
Sincerely,
Anthony Thomas
"wei xiao[MS]" <weix@.online.microsoft.com> wrote in message
news:41beb22b$1@.news.microsoft.com...
The one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
--
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.com...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark