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 designing view

Hi folks,
I've got a view, illustrated at www.dbconsult.dk/ms/sqlquestion.jpg.
There must be a smarter way of doing it, but I can't figure it out:
I wish to get rid of the CASE's.
For each row in tblFejlantal I wish for each row in tblFejlKoder to be
listet as a column.
In the illustration the alias F10/F11/etc equals tblFejlKode.FejlKode,
but with a prefix of "F".
Thanks in advance for your efforts! ;-)
Regards /SnedkerIf you can't alter the design of this database and/or you can't or simply
don't want to do this kind of transformations on the client (I'm guessing
it's for presentation purposes) maybe you might find a few pointers in this
article by Itzik Ben-Gan:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML|||On Tue, 13 Sep 2005 04:19:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
If you have suggestions to the design, I'm all ears! Anything goes.
Regards /Snedker

>If you can't alter the design of this database and/or you can't or simply
>don't want to do this kind of transformations on the client (I'm guessing
>it's for presentation purposes) maybe you might find a few pointers in this
>article by Itzik Ben-Gan:
>http://www.windowsitpro.com/Article...15608.html?Ad=1
>
>ML|||Either post the requirements regarding this specific element of your system
or post DDL and some sample data.
In any case when designing a data-centric solution follow these basic
principles:
1) identifiy all entities, starting with the essential ones;
2) identify relationships between entities; and
3) identify attributes of all entities, with special attention to those
required by genuine business needs.
ML|||On Tue, 13 Sep 2005 05:17:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
Thanks for your response.
As for the database design, normalizing should be okay.
Let's say we have two tables, tblPrimary and tblForeign.
tblPrimary has three columns with one row:
PrimaryID ErrValue ForeignID
1 21.7 5
tblForeign has two columns with 5 rows
ForeignID ErrorCode
1 10
2 20
3 30
4 40
5 50
My view should look like:
PrimaryID Err10 Err20 Err30 Err40 Err50
1 0 0 0 0 21.7
If a row (6, 55) is added to tblForeign the result would be
PrimaryID Err10 Err20 Err30 Err40 Err50
Err55
1 0 0 0 0 21.7 0
I want each row in tblForeign represented in my view as a column.
/Snedker

>Either post the requirements regarding this specific element of your system
>or post DDL and some sample data.
>In any case when designing a data-centric solution follow these basic
>principles:
>1) identifiy all entities, starting with the essential ones;
>2) identify relationships between entities; and
>3) identify attributes of all entities, with special attention to those
>required by genuine business needs.
>
>ML|||Based on the sample you posted I strongly believe the solution described in
Itzik's article is the way to go for you.
Now, turning it into a view is a bit trickier. One way that I can think of
(and works) is a not-really-best-practice solution involving OPENQUERY.
E.g.:
create view dbo.CrossTabbedView
as
select <column list>
from openquery(<server_name>, 'exec <procedure name> <parameters>')
As I said it might work as expected, yet it's pretty resource-intensive. On
the other hand, if you expect it to be fast, it may not "work as expected".
:)
ML

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 Designing a DB

I am trying to build something similar to www.alienware.com where it lets you build your own computer. I was wondering if some one could help me design sturcture to do it on my own. I am zero in DB and know little asp. I am trying to do it for my own site.
ThanksCan you list all of the things you need to know about?

Make a list...

Modem, Modem speed, Modem Type, CPU, CPU Speed, Memroy, Memory Amount...

Once your list is complete, group like itemas in to containers/Entities

Mak sure when you make a list to collect what type of data it is, number date, char, and it's size..

Make a definition for each as well...this will be helpful...

Then devlop rules...

a computer may hav 0 to many hard drives...

So you may have a computer table and a hard drive...

Now you know you need a ComputerHardrive table to show which Computer has which Harddrive...

what'dya think?

Oh, and google up data modeling...

Once that's done we can discuss Physical implementation...|||How you design the database involves more than just determining what you want to store. You also have to consider performance, usage, functionality, et al.

But a simple layout would be:

Table: CPU
CPUName-varchar(50)
CPUSn-varchar(25)
CPUManu-varchar(50)

Table: Monitor
MonModel-varchar(50)
MonSerial-varchar(25)
MonDesc-varchar(150)

Table: Storage
Type-varchar(50)
Desc-varchar(100)

And so on..

Basically, tables for the different options, a customer table, order table, and a table to hold what the basics are for your clients. Of course, a better way would be tables to hold the various peripherals joined to whatever index (key) system you want and so on.

If you have MS Access, monkey around with it, get the layout you want then upsize it to SQL Server.|||Originally posted by acral
You also have to consider performance, usage, functionality, et al.


Yeah, That's the physical part...

A sound, solid, logical model is THE place to start...

Help - Replication Architecture Suggestions

Hi,
I need some help in designing the physical architecture of my database
system for replication. Here is my scenario
I have 1 server each in North America, Europe and Asia. They will be
connected 99 % of the time. All the data needs to be replicated between these
servers. There is very less data which will create contain conflicts while
replicating.
What type of replication should i be having ?
Should i go with the local distributors ?
If there is a conflict and i have local distributors then how can that be
resolved between 3 subscribers ? If possible please provide examples
The above is one scenario
Now each of these servers will be connected by local servers running MSDE.
They do not have a guaranteed connection. They need to upload data and also
recieve fresh updates from these regional servers. There can be a conflict
between the data of 2 local servers which will need to be resolved before
uploading to the regional server. There will be specific conflict policies
defined.
How do i achieve the above ?
I would appreciate if someone can help me in this with examples. I have not
implemented replication till date.
Thanks in advance
Afaq
Afaq,
for this type of scenario, merge replication functions best. There would be
no need to republish and have ultiple distributors - just designate the
'main' server as the publisher (and distributor) and the others as
subscribers. From your description, conflicts can arise as the data is not
partitioned - merge replication will allow you to have column-level or
row-level conflict detection. By default, a change on the publisher will
'win' against a change on the subscribers, and if 2 subscribers conflict,
the first one to synchronize 'wins'. There are other algorithms that can be
used, and you can create your own conflict resolution routine if necessary.
For more info, have a look in BOL for "conflict resolution, overview".
HTH,
Paul Ibison
|||Paul,
Thanks for the reply. Another question i had was regarding the PK for the
tables. I wanted to have identities as the PK. After reading the posts i
realised that identities are not so easy. If i have GUID's then my indexing
performance goes down which is a issue for me.
Can someone suggest me how best i can use identities with merge replication.
I cn use the Not for replciation clause but for that i will have to specify a
range of identity values for a table. That is also acceptable. But how do i
manage when i reach the end of the range? Do i have to manually increase the
range? how to handle this when i have 500 msde's working together. how to
achieve the range on each of these servers.
Any help would be appreciated
Afaq
"Paul Ibison" wrote:

> Afaq,
> for this type of scenario, merge replication functions best. There would be
> no need to republish and have ultiple distributors - just designate the
> 'main' server as the publisher (and distributor) and the others as
> subscribers. From your description, conflicts can arise as the data is not
> partitioned - merge replication will allow you to have column-level or
> row-level conflict detection. By default, a change on the publisher will
> 'win' against a change on the subscribers, and if 2 subscribers conflict,
> the first one to synchronize 'wins'. There are other algorithms that can be
> used, and you can create your own conflict resolution routine if necessary.
> For more info, have a look in BOL for "conflict resolution, overview".
> HTH,
> Paul Ibison
>
>
|||Afaq,
500 MSDE's is a lot
As far as I can see, you have 3 choices:
(a) you can partition the PK values. In this way, each subscriber has an
identity column combined with another column as a PK. The identity column
functions as a normal one - no partitioning - and the other column
identifies the subscriber. In this way there will be no conflicts and no
need for ranges.
(b) you can use automatic range management. An integer column goes to
+2billion (ish), so you should be able to assign large ranges which will
never need to be updated. If you use small ranges, when they are full up the
check constraint will error and new records can't be entered until the
subscriber synchronizes and you want to avoid this possibility.
(c) you can manually assign the ranges. For this you'd need to set up each
subscriber separately and this might turn out to be too much work...
HTH,
Paul Ibison
|||I guess the first option is doable.
Now there is another issue
All my servers are publishers and subscribers. So please consider the
following example
Table A is an article in a publication on Server X. Server Y is a subscriber
to this pubication. Server Y also can be a publisher for Table A and Server X
can be a subscriber for the same. WHen i have a subscription on table A from
server Y i cant have a publication for the same table from Server Y.
How do i achieve the same? I need Table A to be a part of different
publications from different servers as they are in different time zones and
they will publish the data on different times. And we want each of the
servers to publish the data for the same table at different times.
I hope the issue is properly conveyed. Forgive me for asking this question
as i am a newbie in replication.
Thanks
Afaq
"Paul Ibison" wrote:

> Afaq,
> 500 MSDE's is a lot
> As far as I can see, you have 3 choices:
> (a) you can partition the PK values. In this way, each subscriber has an
> identity column combined with another column as a PK. The identity column
> functions as a normal one - no partitioning - and the other column
> identifies the subscriber. In this way there will be no conflicts and no
> need for ranges.
> (b) you can use automatic range management. An integer column goes to
> +2billion (ish), so you should be able to assign large ranges which will
> never need to be updated. If you use small ranges, when they are full up the
> check constraint will error and new records can't be entered until the
> subscriber synchronizes and you want to avoid this possibility.
> (c) you can manually assign the ranges. For this you'd need to set up each
> subscriber separately and this might turn out to be too much work...
> HTH,
> Paul Ibison
>
>

HELP - Designing a Subscripion System for a Group of Reports

We're developing an ASP.NET 2.0 (C#) application that will act as a
front-end to SQL Server 2005 Reporting Services. We have an interesting
requirement that [we believe] can't be solved using RS out-of-the-box. So,
I'm writing today to get some ideas on possible solutions.
The requirement is that the app. needs to support, what we're calling, a
report group. A report group is simply a group of similar reports that can
be executed as a single unit (instead of having to execute each report
individually). This was not too bad to solve. We simply created our own
database tables that track which report group contains which reports.
The tricky part is that we now have to figure out a way to create a
subscription that executes the report group (thereby executing each
individual report in the report group automatically). Creating a
subscription for a single report is fairly straightforward, but it appears
this concept of a report group is not a part of RS.
Does anyone out there have any good ideas/suggestions on how we can
implement such functionality?
The best thought we have so far is to write another application that manages
the execution of each report in a report group. So, the SQL Server 2005 job
would call this additional little application, this additional app. would
lookup which reports are in the report group, and then it would loop through
all the reports and execute them one-by-one. But, this has some issues-like
how do we handle the output type (PDF, Excel, etc.) and how do we handle the
destination (e-mail, printer, etc.) of each report. Plus, it seems like we
would be somewhat reinventing the wheel as far as subscriptions go.
Thanks.Why aren't you using one master report with all the other reports as
sub-reports?
Tom Bizannes
oiduts wrote:
> We're developing an ASP.NET 2.0 (C#) application that will act as a
> front-end to SQL Server 2005 Reporting Services. We have an interesting
> requirement that [we believe] can't be solved using RS out-of-the-box. So,
> I'm writing today to get some ideas on possible solutions.
> The requirement is that the app. needs to support, what we're calling, a
> report group. A report group is simply a group of similar reports that can
> be executed as a single unit (instead of having to execute each report
> individually). This was not too bad to solve. We simply created our own
> database tables that track which report group contains which reports.
> The tricky part is that we now have to figure out a way to create a
> subscription that executes the report group (thereby executing each
> individual report in the report group automatically). Creating a
> subscription for a single report is fairly straightforward, but it appears
> this concept of a report group is not a part of RS.
> Does anyone out there have any good ideas/suggestions on how we can
> implement such functionality?
> The best thought we have so far is to write another application that manages
> the execution of each report in a report group. So, the SQL Server 2005 job
> would call this additional little application, this additional app. would
> lookup which reports are in the report group, and then it would loop through
> all the reports and execute them one-by-one. But, this has some issues-like
> how do we handle the output type (PDF, Excel, etc.) and how do we handle the
> destination (e-mail, printer, etc.) of each report. Plus, it seems like we
> would be somewhat reinventing the wheel as far as subscriptions go.
> Thanks.|||You could try setting up a shared schedule. It would still require
individual subscriptions for each report but they would all start
firing at the same scheduled time.