Help in database design

I am designing an inventory database in which I need some help, I have the following entities:

Items
Notebook
Wireless Card
ADSL Modem
etc...
Models
Acer centrino 1.6
Acer centrino 1.73
3COM 4x1
25 hours Wi-fi access
50 hours Wi-fi access
etc...

Packages
Package A:
Acer centrino 1.6
3COM 4x1
25 hours Wi-fi access
Package B:
Acer centrino 1.73
3COM 4x1
50 hours Wi-fi access
I made a table for the items having the following fields: Item_ID, Item_Name
and another for the Models having: Model_ID, Model_Name, Item_Type(Foreign Key to Items table)
up to this point is this correct?
About the packages table, I don't know if it is correct to have a field for each model (one for notebook, other for modem, and other for wireless card) like this it would be like having 3 foreign keys to the same table but nothing distinguishes them
I don't know how to relate the packages and the models table.
Any recommendations for a proper design for those entities?

I would use the relational data model, and design it as follows. I really can't show you how the Items table is to be implemented in this schema as I haven't seen how you intend to use that table yet.

Table: Items
Columns: ItemID, ItemDescription

Table: Models
Columns: ModelID, ModelDescription

Table: Packages
Columns: PackageID, PackageDescription

Table: PackageModels
Columns: PackageID, ModelID

|||Also look like a relationship (1-M) between item and model although the naming seems strange.|||

Thanks for your replies,but they were a little bit late, I already finished the project

Here is the design I used, maybe it would be useful for anyone who might face something like that.

Items Table

Item_ID, Item_Name

Models Table

Model_ID, Model_Name, ItemType(FK to Items table,i.e. this model is of which item type)

Packages Table

Package_ID, Package_Name

Package_ItemModel

Package_ID, Item_ID, Model_ID

|||I'm suprised that Model isn't independant of item.|||

Why?

I need to specify whether hp nx6110 is a notebook,router or card and so on for other types.

|||argh, sorry the naming has confused me yet again.

Help for joining tables (2nd try ;o) )

Okay so I'll give it another try
I have an assigment design a web solution with a presention of energy
consumptions from some appartments.
All appartments are identical and have 5 meters, the only difference is the
appartment number (3 digits) e.g. "105" all meters gets logged once every
night at 02.00 AM.
My idea is to work with a collection of "predefined" meters e.g. and join
them with the logged data, that's why I have the meters table, then I can
also add additional meters without changeing code.
The consumption presentation is done per appartment, meaning that the owner
of the appartment logs on to the system and the appartment ID gets
identified e.g. 105. When the owner has logged on the energy data for the
related appartment will be presented.
I have three tables
Meters m (defined by me)
Analog register (defined by the log system)
Analog values (defined by the log system)
the relation between analog values is the field "ID" PK in Analog Register
AR, FK in Analog Values AV
this script joins and summerize the energy data, so far so good!
Select ar.[name], av.id, count(*) as NoOfLogs,
Sum(Case WHEN Datediff(Mi, timedate, getdate()) < 60 THEN av.Val ELSE 0
END) as [Last Hour],
Sum(av.val) as Total
From [Analog register] AR INNER JOIN [analog values] AV
On AR.[ID] = AV.[ID]
--where ar.[name] in (select [name] from meters m where type = 1)
GROUP by ar.[name], av.id
select * from meters
--where ar.[name] in (select [name] from meters m where type = 1)
The where caluse is the part I try to replace with something like
"where meters.name+@.appartmentnumber like ar.name"
What I need is to see if meters.name plus the appartmendnumber is a
substring of ar.name.
With the above "where clause" it only works if there's an exact match.
@.appartmentnumber is a variable I add to the query.
I hope this makes my needs more clear'
regards
Henry
****** DDL
CREATE TABLE [dbo].[Meters] (
[Meter_id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [int] NULL ,
[Unit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Designation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0DA234BA-12AA-43E6-B94C-004F265F82D8','Solfanger',2,
'kwh','Solenergi')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('EDC2D54C-704D-4DE3-AFB2-1A2584BFB077','And',1,'Kwh',
'Energi')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('F3C42A01-098C-48F8-A4D3-2F4F120AE597','Anders',1,'Mw
h','Elmler')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0A4FF86E-0F66-44FB-8022-B9FC90630F22','X5991_P07_inp
ut_Accum',1,'m3','Gas-mler')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('046B8001-795D-4C61-8B45-CA9B3BB02EEF','X5991_P06_inp
ut_Accum',1,'m3','Varmtvandsmler')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0DBE4542-F741-4D60-A9B1-DAE4DA69AD10','X5991_P04_inp
ut_Accum',1,'Kwh','El-mler')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('20C1A7CB-041E-47E0-B0C7-DD7F54C6CC41','SupplyTemp.Ma
x',1,'m3','Testmler1')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('A8E1FA35-225C-414D-849C-F5256D2A2845','ReturnTemp',1
,'kwh','Testmler2')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('FDDE06DB-E501-4748-A514-FCB9B99594C4','X5991_P05_inp
ut_Accum',1,'m3','Koldtvandsmler')
CREATE TABLE [dbo].[Analog Register] (
[Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID] [int] NOT NULL ,
[Category] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [smallint] NOT NULL ,
[Val] [float] NULL ,
[ValueTime] [datetime] NULL ,
[MinValue] [float] NULL ,
[MaxValue] [real] NULL ,
[SignalText] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SigType] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Unit] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaxRawTimeLength] [int] NULL ,
[CompactTimeLength0] [int] NULL ,
[CompactTime0] [datetime] NULL ,
[CompactVal0] [float] NULL ,
[CompactFlags0] [real] NULL ,
[CompactTimeLength1] [int] NULL ,
[CompactTime1] [datetime] NULL ,
[CompactVal1] [float] NULL ,
[CompactFlags1] [real] NULL ,
[CompactTimeLength2] [int] NULL ,
[CompactTime2] [datetime] NULL ,
[CompactVal2] [float] NULL ,
[CompactFlags2] [real] NULL ,
[CompactTimeLength3] [int] NULL ,
[CompactTime3] [datetime] NULL ,
[CompactVal3] [float] NULL ,
[CompactFlags3] [real] NULL
) ON [PRIMARY]
GO
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. OutdoorTemp',1,'LogSignals','Library2',1
,-1.042895
793914795e+000,'Jun
7 2006 12:00:00:000AM','Mler1')
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. SupplyTemp',2,'LogSignals','Library2',1,
6.07073631
2866211e+001,'Jun
7 2006 12:00:00:000AM','Mler2')
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. SupplyTempControl_SetP',3,'LogSignals','
Library2',
1,7.500000000000000e+001,'Jun
7 2006 12:00:00:000AM','Mler3')
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. ReturnTemp',4,'LogSignals','Library2',1,
2.83133945
4650879e+001,'Jun
7 2006 12:00:00:000AM','Mler4')
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2.SupplyTemp.Max',5,'LogSignals','Library2',3,6.0707
56912231445e+001,'Jun
6 2006 12:00:00:000AM','Mler5')
I had to ommit some of the irelevant data from the AR table.
CREATE TABLE [dbo].[Analog Values] (
[ID] [int] NOT NULL ,
[TimeDate] [datetime] NOT NULL ,
[TimeLength] [int] NULL ,
[Flags] [int] NULL ,
[Val] [float] NULL ,
[Count] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
5:49:00:903PM',3600,1,1.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
5:49:00:903PM',3600,1,1.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
5:49:00:903PM',3600,1,1.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
5:49:00:903PM',3600,1,1.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
5:52:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
7:34:00:547PM',3600,1,1.903548486012570e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
7:34:00:750PM',3600,1,3.907330979177984e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
7:34:00:983PM',3600,1,5.911672461481145e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
7:36:00:653PM',3600,1,2.905523580965939e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
7:36:00:827PM',3600,1,4.908765717964866e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
7:36:00:903PM',3600,1,5.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 24 2006
12:06:00:857AM',3600,1,1.909324707102612e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 24 2006
12:06:00:920AM',3600,1,2.910498584291879e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 24 2006
12:06:00:997AM',3600,1,3.911933323078760e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 24 2006
12:06:01:043AM',3600,1,4.912790439756638e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 24 2006
12:06:01:090AM',3600,1,5.913666189405772e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 25 2006
2:23:00:140AM',3600,1,1.895964866710483e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 25 2006
2:23:00:263AM',3600,1,3.898256722175241e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 25 2006
2:23:00:373AM',3600,1,5.900306349013643e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 25 2006
2:24:00:200AM',3600,1,2.897082844985975e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 25 2006
2:24:00:327AM',3600,1,4.899449232335766e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 25 2006
2:26:00:140AM',3600,1,1.895964866710483e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 25 2006
2:26:00:200AM',3600,1,2.897082844985975e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 25 2006
2:26:00:263AM',3600,1,3.898256722175241e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 25 2006
2:26:00:327AM',3600,1,4.899449232335766e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 25 2006
2:26:00:390AM',3600,1,5.900623109525032e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
1:08:00:687AM',3600,1,1.906157101988718e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
1:08:00:763AM',3600,1,2.907573207804341e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
1:08:00:840AM',3600,1,3.909007946591222e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 26 2006
1:08:00:950AM',3600,1,4.911057573429624e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
1:08:01:030AM',3600,1,5.912548211130281e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
1:09:00:670AM',3600,1,1.905840341477328e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
1:09:00:797AM',3600,1,3.908206728827120e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
1:09:00:920AM',3600,1,5.910498584291879e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
1:10:00:733AM',3600,1,2.907014218666595e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
1:10:00:797AM',3600,1,3.908206728827120e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
6:27:00:687AM',3600,1,1.906157101988718e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
6:27:00:747AM',3600,1,2.907275080264210e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
6:27:00:810AM',3600,1,3.908448957453476e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 26 2006
6:27:00:873AM',3600,1,4.909622834642743e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
6:27:00:937AM',3600,1,5.910815344803268e+000,1)Henry,

> What I need is to see if meters.name plus the appartmendnumber is a
> substring of ar.name.
...
where ar.name like '%' + meters.name + @.appartmentnumber + '%'
AMB
meters.name+@.appartmentnumber like
"Henry" wrote:

> Okay so I'll give it another try
> I have an assigment design a web solution with a presention of energy
> consumptions from some appartments.
> All appartments are identical and have 5 meters, the only difference is th
e
> appartment number (3 digits) e.g. "105" all meters gets logged once every
> night at 02.00 AM.
> My idea is to work with a collection of "predefined" meters e.g. and join
> them with the logged data, that's why I have the meters table, then I can
> also add additional meters without changeing code.
> The consumption presentation is done per appartment, meaning that the owne
r
> of the appartment logs on to the system and the appartment ID gets
> identified e.g. 105. When the owner has logged on the energy data for the
> related appartment will be presented.
> I have three tables
> Meters m (defined by me)
> Analog register (defined by the log system)
> Analog values (defined by the log system)
> the relation between analog values is the field "ID" PK in Analog Register
> AR, FK in Analog Values AV
> this script joins and summerize the energy data, so far so good!
> Select ar.[name], av.id, count(*) as NoOfLogs,
> Sum(Case WHEN Datediff(Mi, timedate, getdate()) < 60 THEN av.Val ELSE 0
> END) as [Last Hour],
> Sum(av.val) as Total
> From [Analog register] AR INNER JOIN [analog values] AV
> On AR.[ID] = AV.[ID]
> --where ar.[name] in (select [name] from meters m where type = 1)
> GROUP by ar.[name], av.id
> select * from meters
> --where ar.[name] in (select [name] from meters m where type = 1)
> The where caluse is the part I try to replace with something like
> "where meters.name+@.appartmentnumber like ar.name"
> What I need is to see if meters.name plus the appartmendnumber is a
> substring of ar.name.
> With the above "where clause" it only works if there's an exact match.
> @.appartmentnumber is a variable I add to the query.
> I hope this makes my needs more clear'
>
> regards
> Henry
>
> ****** DDL
>
> CREATE TABLE [dbo].[Meters] (
> [Meter_id] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [int] NULL ,
> [Unit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Designation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0DA234BA-12AA-43E6-B94C-004F265F82D8','Solfanger',
2,'kwh','Solenergi')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('EDC2D54C-704D-4DE3-AFB2-1A2584BFB077','And',1,'Kwh
','Energi')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('F3C42A01-098C-48F8-A4D3-2F4F120AE597','Anders',1,'
Mwh','Elmler')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0A4FF86E-0F66-44FB-8022-B9FC90630F22','X5991_P07_i
nput_Accum',1,'m3','Gas-mler')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('046B8001-795D-4C61-8B45-CA9B3BB02EEF','X5991_P06_i
nput_Accum',1,'m3','Varmtvandsmler')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0DBE4542-F741-4D60-A9B1-DAE4DA69AD10','X5991_P04_i
nput_Accum',1,'Kwh','El-mler')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('20C1A7CB-041E-47E0-B0C7-DD7F54C6CC41','SupplyTemp.
Max',1,'m3','Testmler1')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('A8E1FA35-225C-414D-849C-F5256D2A2845','ReturnTemp'
,1,'kwh','Testmler2')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('FDDE06DB-E501-4748-A514-FCB9B99594C4','X5991_P05_i
nput_Accum',1,'m3','Koldtvandsmler')
>
> CREATE TABLE [dbo].[Analog Register] (
> [Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ID] [int] NOT NULL ,
> [Category] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [smallint] NOT NULL ,
> [Val] [float] NULL ,
> [ValueTime] [datetime] NULL ,
> [MinValue] [float] NULL ,
> [MaxValue] [real] NULL ,
> [SignalText] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SigType] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Unit] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MaxRawTimeLength] [int] NULL ,
> [CompactTimeLength0] [int] NULL ,
> [CompactTime0] [datetime] NULL ,
> [CompactVal0] [float] NULL ,
> [CompactFlags0] [real] NULL ,
> [CompactTimeLength1] [int] NULL ,
> [CompactTime1] [datetime] NULL ,
> [CompactVal1] [float] NULL ,
> [CompactFlags1] [real] NULL ,
> [CompactTimeLength2] [int] NULL ,
> [CompactTime2] [datetime] NULL ,
> [CompactVal2] [float] NULL ,
> [CompactFlags2] [real] NULL ,
> [CompactTimeLength3] [int] NULL ,
> [CompactTime3] [datetime] NULL ,
> [CompactVal3] [float] NULL ,
> [CompactFlags3] [real] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. OutdoorTemp',1,'LogSignals','Library2',1
,-1.0428
95793914795e+000,'Jun
> 7 2006 12:00:00:000AM','Mler1')
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. SupplyTemp',2,'LogSignals','Library2',1,
6.070736
312866211e+001,'Jun
> 7 2006 12:00:00:000AM','Mler2')
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. SupplyTempControl_SetP',3,'LogSignals','
Library2
',1,7.500000000000000e+001,'Jun
> 7 2006 12:00:00:000AM','Mler3')
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. ReturnTemp',4,'LogSignals','Library2',1,
2.831339
454650879e+001,'Jun
> 7 2006 12:00:00:000AM','Mler4')
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2.SupplyTemp.Max',5,'LogSignals','Library2',3,6.07
0756912231445e+001,'Jun
> 6 2006 12:00:00:000AM','Mler5')
> I had to ommit some of the irelevant data from the AR table.
>
> CREATE TABLE [dbo].[Analog Values] (
> [ID] [int] NOT NULL ,
> [TimeDate] [datetime] NOT NULL ,
> [TimeLength] [int] NULL ,
> [Flags] [int] NULL ,
> [Val] [float] NULL ,
> [Count] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
> 5:49:00:903PM',3600,1,1.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
> 5:49:00:903PM',3600,1,1.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
> 5:49:00:903PM',3600,1,1.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
> 5:49:00:903PM',3600,1,1.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
> 5:52:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
> 7:34:00:547PM',3600,1,1.903548486012570e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
> 7:34:00:750PM',3600,1,3.907330979177984e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
> 7:34:00:983PM',3600,1,5.911672461481145e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
> 7:36:00:653PM',3600,1,2.905523580965939e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
> 7:36:00:827PM',3600,1,4.908765717964866e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
> 7:36:00:903PM',3600,1,5.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 24 2006
> 12:06:00:857AM',3600,1,1.909324707102612e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 24 2006
> 12:06:00:920AM',3600,1,2.910498584291879e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 24 2006
> 12:06:00:997AM',3600,1,3.911933323078760e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 24 2006
> 12:06:01:043AM',3600,1,4.912790439756638e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 24 2006
> 12:06:01:090AM',3600,1,5.913666189405772e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 25 2006
> 2:23:00:140AM',3600,1,1.895964866710483e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 25 2006
> 2:23:00:263AM',3600,1,3.898256722175241e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 25 2006
> 2:23:00:373AM',3600,1,5.900306349013643e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 25 2006
> 2:24:00:200AM',3600,1,2.897082844985975e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 25 2006
> 2:24:00:327AM',3600,1,4.899449232335766e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 25 2006
> 2:26:00:140AM',3600,1,1.895964866710483e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 25 2006
> 2:26:00:200AM',3600,1,2.897082844985975e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 25 2006
> 2:26:00:263AM',3600,1,3.898256722175241e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 25 2006
> 2:26:00:327AM',3600,1,4.899449232335766e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 25 2006
> 2:26:00:390AM',3600,1,5.900623109525032e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
> 1:08:00:687AM',3600,1,1.906157101988718e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
> 1:08:00:763AM',3600,1,2.907573207804341e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
> 1:08:00:840AM',3600,1,3.909007946591222e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 26 2006
> 1:08:00:950AM',3600,1,4.911057573429624e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
> 1:08:01:030AM',3600,1,5.912548211130281e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
> 1:09:00:670AM',3600,1,1.905840341477328e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
> 1:09:00:797AM',3600,1,3.908206728827120e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
> 1:09:00:920AM',3600,1,5.910498584291879e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
> 1:10:00:733AM',3600,1,2.907014218666595e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
> 1:10:00:797AM',3600,1,3.908206728827120e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
> 6:27:00:687AM',3600,1,1.906157101988718e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
> 6:27:00:747AM',3600,1,2.907275080264210e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
> 6:27:00:810AM',3600,1,3.908448957453476e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 26 2006
> 6:27:00:873AM',3600,1,4.909622834642743e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
> 6:27:00:937AM',3600,1,5.910815344803268e+000,1)
>
>sql

Help designing tables

HI gurus,

I need help with database design. I am doing a bowling league program.
1. each bowling center has 1 or more leagues
2. each league has 2 or more teams
3. each team has 2 or more bowlers
4. each week each team bowls 3 games
5. summer leagues last ~13 weeks
winter leagues last ~36 weeks

Tables I have + is primary key FK is foreign key
BowlingCenter
+ CenterID
......

League
+ LeagueID
FK CenterID
.......

Team
+ TeamID
FK LeagueID

Bowler
+ BowlerID
FK TeamID

Here I am lost. How do I do the 3 games a week for 13 or 36 weeks AND associate the 3 games each week with each bowler?

Any help would be appreciated.

What about:

Bowling Center (PK CenterId
)

|

League (PK LeagueId) (PK CenterId
)

Team(PK TeamId)

TeamInLeagues (PK TeamId, PK LeagueId) (FK TeamId)(fK LeagueId) --In order to make the team available to other Leagues.

Bowler (PK BowlerId)

BowlerTeam (PK BowlerId, PK TeamId) (FK BowlerId) (FK TeamId)
--In order to assign a bowler to many teams

Games
(Weekid, BowlerTeam_PK) (FK BowlerTeam_PK)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, I'll give it a try.

Help Designing an App. to be Run from a Job

Hello,

I'm working on a project that uses SQL Server 2005 and C# 2.0. I need some ideas of the best way to design/implement an application that will be executed from a SQL Server job.

The general idea is: a SQL Server job will call [something] and pass a couple of parameters to [something], then [something] will query a database and gather other information, then [something] will do some processing of data, and finally [something] will output the results for end user consumption.

The problem is that I don't know what [something] should be. Should it be a C# executable (but it can have no UI)? Should it be a web service? Should it be a console application (but, again, it cannot have a UI)? Should the job call a stored procedure, which in turn calls a C# assembly?

Basically, I'm just trying to get some ideas of the best way to design/implement a solution to my situation.

Thanks.

Hi,

without further information, I would suggest to use a c# console app called from the job.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the input.

What other information could I provide to help choose the best solution?

|||

Best thing is to call a Stored Proc from the Sql job

In the Stored proc gather all the parameters that you need and use BCP out to keep that data in a flat file.

There is no need to use Webservice or other C# program as this would cause an additional overhead.

|||

There are a number of things your description suggests, and others that it leaves open...

1 - from what you say, it looks like the something does not need access to the resources of the server (as a computer) and only needs access to data inside SQL Server. If this is true, then the use of a SQL Server job to invoke a stored procedure is the way to go (and in SQL Server 2005 you can write your stored procedure in C# if you like).

If, on the other hand, you need access to information outside SQL Server (files, I/O, Active Directory, other PCs, ...) you'll need an app running outside SQL Server (yes - you can invoke an app from inside SQL Server, but why jump through hoops when you don't need to?). If this is the road to take, use Windows' scheduler instead of SQL Server's scheduler.

If you're running inside SQL Server, as one of the other posts suggested, you'll have a Stored Proc to gather parameters, that likely will call another (or more) to do the work. If you're doing stuff outside the database server the app will likely invoke a stored proc to do the 'inside' work - so you're just partitioning where you do what work.

When returning the data to the user - there are a number of options that depend on various factors you've not mentioned as to which is better:

> simply write the data to an output table (with a timestamp on each row if you need to hold multiple day's / run's worth of output) - and the use can have a simple reporting app to read from it.

> If the volume of data is small SQL Server can email the recipient(s) - again, this is probably better if the number of recipients is small. You may also need to consider the security of the data as it travels as email.

> If you need to store the data outside the server (in a dated file, for example) you have to get it out, somehow. If your app is already running outside the server that's a no brainer; if everything is running inside the server I think you'll have to trigger some outside app to pull the data (i.e. I don't think there's a way for a SQL Server job to dump data to the outside world). DO NOT TAKE MY WORD for this - DTS may be invokable as a SQL Server job and may server to store data outside the server, or there may be other capabilities of SQL Server 2005 that I've not encountered yet.

> Worst comes to worst - you have a simple outside app that polls a table in the server for a 'ready' flag and then pulls the data. But that's ugly.

HTH

Griffin

sql

Help - Multi values dimensions ?

Hello all
we developped a software with a data warehouse in sql server, without using
analysis services, and following the star design.
we are working with data about transactions, with caracteristics modeled in
dimensions. in general each transaction has one single value for each
caracteristic, but for one, it may have more than one or no value.
some solution we thought about:
-we use comma separated IDs (string) in the dimension in the fact table, but
it would hurt indexing
-or store the IDs in a separate table, but each query will require a join
my question is what is the best way to implement this feature ?
thank you for your help
its a standard "n-n" relationship.
1 caracteristic, can have 0 to N transacions
1 transaction can have 0 to N caracteristic
So, you'll have 3 tables:
Caracteristics 1 - n CaractToTransac n - 1 Transactions
The CaractToTransac table contain only 2 columns:
CaracteristicID and TransactionID
(maybe you can add a column called "sequence" if the order of your
caracteristics is important for you)
Now you have multi caracteristics for each transaction.
"r_samir" <r_samir@.discussions.microsoft.com> a crit dans le message de
news:8E2216BF-50D7-4FF6-89A3-C1995D83E3B5@.microsoft.com...
> Hello all
> we developped a software with a data warehouse in sql server, without
using
> analysis services, and following the star design.
> we are working with data about transactions, with caracteristics modeled
in
> dimensions. in general each transaction has one single value for each
> caracteristic, but for one, it may have more than one or no value.
> some solution we thought about:
> -we use comma separated IDs (string) in the dimension in the fact table,
but
> it would hurt indexing
> -or store the IDs in a separate table, but each query will require a join
> my question is what is the best way to implement this feature ?
> thank you for your help