Newbie need help for database design

Hi,

I am designing a table for classrooms with many features. Following is
the detail of the table (let's say the name of table is CRoom):

1. Room Name (key)
2. Building name (foreign key)
3. Capacity
4. Chairs
5. T-arm chairs
6. Tables
7. Desks
8. Lectern
9. 35 mm slide projector
10. Dual 35 mm slide projector
11. 3/4" video player
12. 1/2" video player
13. Chalkboard
14. Markerboard
.....

My questions are:
a. Should I put everything in the same table?
or
b. If there is chairs in a classroom then there will be no T-arm
chairs there,
vice versa. So should I create a sepearte table for chairs and
t-arm chairs
and use the key for this as a foreign key in the CRoom table?
c. For number 11 and 12, some classrooms have only 3/4" video player,
some
classrooms have only 1/2" video player and some classrooms have
both.
Should I create another table for them and set value 1 for 3/4"
video player
2 for 1/2" video player and 3 for both? Then use the value 1, 2, 3
in the
CRoom table to represent the three differnt situations. If this is
not
proper, then how should I deal with this one?

Thanks a lot in advance.>> I am designing a table for classrooms with many features. <<

Some of the attributes are part of the room itself, like square
footage, maximum allowed seating, and chalkboards that are bolted to
the walls. You can put those attributes into the Classrooms table.
Things that can be moved around (slide projector, video player) would
be in other tables and assigned to classrooms by an inventory location
table.

Newbie designing a database

Hi,
I am currently designing my first SqlSever database and I'm new to
replication.
For now, I'll be using the database for one single location (Shop). But
I'm already planning to use replication in the future for a second shop
(a branch actually).
I have read a lot with the Books Online about replication, but for now,
it's just too much to learn. We first need to be able to start using the
database ASAP, once the design is correct, we will extend it to be used
on other sites also.
So my questiong is: do I need to design the database now to be
replicatable, or will it be easy later to add replication to the database?
Michael,
the main issues related to the schema that I can think of off the top of my
head are:
(1) you must have a PK to use transactional replication
(2) you will not be able to use queued updating subscribers and update BLOB
columns
(3) sometimes you'll need to consider partitioning a table and replicating
only one partition (where colid is large)
(4) in transactional replication it is usual to have triggers fire only on
the publisher (use NOT FOR REPLICATION)
(5) in merge replication, we usually use NOT FOR REPLICATION for identity
columns
(6) in merge replication we usually use NOT FRO REPLICATION for foreign
keys.
After that, we'd need to hear more about your particualr needs to recommend
a replication setup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
It's good to get a start here.
Our company has 2 shops on different location.
We would like to setup an inventory system, so we can use it in both shops.
Having a central database for both shops would be impossible. Our
internet connection is really slow...
So we came up with replication.
Both shops are quite independant despite we sell the same products.
Sometime one shop will have one product and the other not, or will only
have it at a later time.
We will not synchronise the database very often.
I think, a merge replication will do the job here, except for the new
products arriving in one shop before the other. We need a way to declare
the same product on both database (I still cannot figure it out yet how
to do)
So here is just a few thoughts I have now. We still have a long way to
go before being able to fully computerise both shops...
Thank you for your prompt response, I'm very grateful.
Paul Ibison wrote:
> Michael,
> the main issues related to the schema that I can think of off the top of my
> head are:
> (1) you must have a PK to use transactional replication
> (2) you will not be able to use queued updating subscribers and update BLOB
> columns
> (3) sometimes you'll need to consider partitioning a table and replicating
> only one partition (where colid is large)
> (4) in transactional replication it is usual to have triggers fire only on
> the publisher (use NOT FOR REPLICATION)
> (5) in merge replication, we usually use NOT FOR REPLICATION for identity
> columns
> (6) in merge replication we usually use NOT FRO REPLICATION for foreign
> keys.
> After that, we'd need to hear more about your particualr needs to recommend
> a replication setup.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Newbie # of columns per table

What are the peformance implications of designing tables
that are in excess of 170 columns per table?
Thanks,
Kim> What are the peformance implications of designing tables
> that are in excess of 170 columns per table?
If your application is read intensive, the performance could be acceptable.
If it is write intensive, it really depends whether you have a lot of
denormalized data. Ayway, you should check your design from conceptual and
logical poit of view - 170 columns seems like you need some normalization.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||The application database acts both as an oltp and a dss.
I know, I know.. It's third party application which has a
built in module to create tables, these tables are
currently being built by the end user. I'm also concerned
because these large tables are created with the 90% of
the columns defined as varchar(50) NULL and 90% of the
nullable columns have absolutely no data entries..
>--Original Message--
>> What are the peformance implications of designing
tables
>> that are in excess of 170 columns per table?
>If your application is read intensive, the performance
could be acceptable.
>If it is write intensive, it really depends whether you
have a lot of
>denormalized data. Ayway, you should check your design
from conceptual and
>logical poit of view - 170 columns seems like you need
some normalization.
>--
>Dejan Sarka, SQL Server MVP
>FAQ from Neil & others at: http://www.sqlserverfaq.com
>Please reply only to the newsgroups.
>PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>.
>|||> The application database acts both as an oltp and a dss.
> I know, I know.. It's third party application which has a
> built in module to create tables, these tables are
> currently being built by the end user. I'm also concerned
> because these large tables are created with the 90% of
> the columns defined as varchar(50) NULL and 90% of the
> nullable columns have absolutely no data entries..
Then the problem is a little bit smaller... Varchar columns do not occupy
space if they are empty.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

newbie - getting data out of AS / cube

We are in the beginning phases of designing a data warehouse solution, using
SSAS 2005 and .Net technologies. I know that we can use BI portals such as
from Proclarity, Panorama, Business Objects, etc. However, we wanted to
create our own UI front end (either Web or Winform).
My question is this -- can someone recommend what (and how) .Net
technologies to use to consume the data from SSAS? e.g. in a traditional
OLTP system, I would use sql queries and ado.net to fill my data table, and
maybe use an OR mapper to hydrate custom business objects. The data table
would then be sent to the UI front end for display / control binding and
handling.
For a data warehouse... what would I do differently? Do I still use ado.net
and maybe MDX / XMLA to query the cube? What data structures do I use to
consume the data? A Dataset with relationships? Would you transform that
data to customized business objects? Do you have any strategies on how to
support drill-down on the front end?
Or, would the middle-tier be very "thin" -- meaning the front-end would just
interface to AS directly and we have smart controls to bind to the raw data?
Any pointers would be greatly appreciated.
Thanks
Ray
p.s. I've been cross posting to the two newsgroups -- could someone tell me
what questions belong to which group?
Reporting Portal for OLAP
Hello Ray,
For a quick start have a look at SQL Server 2005 Reporting Services.
It comes free with SQL server and provides a web base portal that you
can customize using .NET and or Sharepoint. You can create your own
app/ portal using RS web services.
RS2005 Reports support MDX natively so you can use Reporting Services
to deliver standard reports against Analysis Services.
New to SQL server 2005 is Report Builder that provides a great web
based UI for creating ad hoc reports and publishing them back to the
Reporting services portal. Report Builder also supports drill through
to underlying data.
Check out
http://www.microsoft.com/technet/pro.../2005ssrs.mspx
Hope this gets you started.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hi,
Thank you for the response. I have looked a little bit into Reporting
Services... I may be a bit weak on the Sharepoint side of things though --
my concern is that the RS2005/Sharepoint didn't offer sufficient
customization abilities. Some things our Web App may need to do:
- controls for boston matrix, various types of chargs, speedometers
- full user session management, security, roles, etc.
- integration with other data sources (e.g. Weather, newsfeed)
- support for user input -- e.g. in cost/budget/forecasting analysis
- support for "advanced" reports -- our organization is proficient with
Crystal Reports, so we may wish to continue using it over RS2005.
I was concerned that the solution/portal offered by Microsoft (or other
vendors) would not meet our requirements. This is why I think it would make
sense if we built our own custom portal using our own technologies.
Hence, my question about the middle-tier -- What type of data constructs
should I be using?
Thank you,
Ray
<Myles.Matheson@.gmail.com> wrote in message
news:1124414479.649578.162120@.g49g2000cwa.googlegr oups.com...
> Reporting Portal for OLAP
> Hello Ray,
> For a quick start have a look at SQL Server 2005 Reporting Services.
> It comes free with SQL server and provides a web base portal that you
> can customize using .NET and or Sharepoint. You can create your own
> app/ portal using RS web services.
> RS2005 Reports support MDX natively so you can use Reporting Services
> to deliver standard reports against Analysis Services.
> New to SQL server 2005 is Report Builder that provides a great web
> based UI for creating ad hoc reports and publishing them back to the
> Reporting services portal. Report Builder also supports drill through
> to underlying data.
> Check out
> http://www.microsoft.com/technet/pro.../2005ssrs.mspx
> Hope this gets you started.
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
|||I found the best way to exchange data between the UI and business layers was
"processed" XML/A.
That is to say, the business layer implements services that receive user
requests, maps them to MDX query patterns, queries the server using Adomd.net
and specifying an XML/A response type, transforms the XML/A to match the
request (table, indicator, chart) and returns it to the UI layer.
Consider that a standard XML/A response will usually include tons of
unnecessary metadata (in a custom app), so you can substantially reduce the
size of the XML/A message before sending it to the UI layer.
HTH,
Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html
"Ray" wrote:

> Hi,
> Thank you for the response. I have looked a little bit into Reporting
> Services... I may be a bit weak on the Sharepoint side of things though --
> my concern is that the RS2005/Sharepoint didn't offer sufficient
> customization abilities. Some things our Web App may need to do:
> - controls for boston matrix, various types of chargs, speedometers
> - full user session management, security, roles, etc.
> - integration with other data sources (e.g. Weather, newsfeed)
> - support for user input -- e.g. in cost/budget/forecasting analysis
> - support for "advanced" reports -- our organization is proficient with
> Crystal Reports, so we may wish to continue using it over RS2005.
> I was concerned that the solution/portal offered by Microsoft (or other
> vendors) would not meet our requirements. This is why I think it would make
> sense if we built our own custom portal using our own technologies.
> Hence, my question about the middle-tier -- What type of data constructs
> should I be using?
> Thank you,
> Ray
> <Myles.Matheson@.gmail.com> wrote in message
> news:1124414479.649578.162120@.g49g2000cwa.googlegr oups.com...
>
>

newbie - getting data out of AS / cube

We are in the beginning phases of designing a data warehouse solution, using
SSAS 2005 and .Net technologies. I know that we can use BI portals such as
from Proclarity, Panorama, Business Objects, etc. However, we wanted to
create our own UI front end (either Web or Winform).
My question is this -- can someone recommend what (and how) .Net
technologies to use to consume the data from SSAS? e.g. in a traditional
OLTP system, I would use sql queries and ado.net to fill my data table, and
maybe use an OR mapper to hydrate custom business objects. The data table
would then be sent to the UI front end for display / control binding and
handling.
For a data warehouse... what would I do differently? Do I still use ado.net
and maybe MDX / XMLA to query the cube? What data structures do I use to
consume the data? A Dataset with relationships? Would you transform that
data to customized business objects? Do you have any strategies on how to
support drill-down on the front end?
Or, would the middle-tier be very "thin" -- meaning the front-end would just
interface to AS directly and we have smart controls to bind to the raw data?
Any pointers would be greatly appreciated.
Thanks
Ray
p.s. I've been cross posting to the two newsgroups -- could someone tell me
what questions belong to which group?Reporting Portal for OLAP
Hello Ray,
For a quick start have a look at SQL Server 2005 Reporting Services.
It comes free with SQL server and provides a web base portal that you
can customize using .NET and or Sharepoint. You can create your own
app/ portal using RS web services.
RS2005 Reports support MDX natively so you can use Reporting Services
to deliver standard reports against Analysis Services.
New to SQL server 2005 is Report Builder that provides a great web
based UI for creating ad hoc reports and publishing them back to the
Reporting services portal. Report Builder also supports drill through
to underlying data.
Check out
http://www.microsoft.com/technet/pr...5/2005ssrs.mspx
Hope this gets you started.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||Hi,
Thank you for the response. I have looked a little bit into Reporting
Services... I may be a bit weak on the Sharepoint side of things though --
my concern is that the RS2005/Sharepoint didn't offer sufficient
customization abilities. Some things our Web App may need to do:
- controls for boston matrix, various types of chargs, speedometers
- full user session management, security, roles, etc.
- integration with other data sources (e.g. Weather, newsfeed)
- support for user input -- e.g. in cost/budget/forecasting analysis
- support for "advanced" reports -- our organization is proficient with
Crystal Reports, so we may wish to continue using it over RS2005.
I was concerned that the solution/portal offered by Microsoft (or other
vendors) would not meet our requirements. This is why I think it would make
sense if we built our own custom portal using our own technologies.
Hence, my question about the middle-tier -- What type of data constructs
should I be using?
Thank you,
Ray
<Myles.Matheson@.gmail.com> wrote in message
news:1124414479.649578.162120@.g49g2000cwa.googlegroups.com...
> Reporting Portal for OLAP
> Hello Ray,
> For a quick start have a look at SQL Server 2005 Reporting Services.
> It comes free with SQL server and provides a web base portal that you
> can customize using .NET and or Sharepoint. You can create your own
> app/ portal using RS web services.
> RS2005 Reports support MDX natively so you can use Reporting Services
> to deliver standard reports against Analysis Services.
> New to SQL server 2005 is Report Builder that provides a great web
> based UI for creating ad hoc reports and publishing them back to the
> Reporting services portal. Report Builder also supports drill through
> to underlying data.
> Check out
> http://www.microsoft.com/technet/pr...5/2005ssrs.mspx
> Hope this gets you started.
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>|||I found the best way to exchange data between the UI and business layers was
"processed" XML/A.
That is to say, the business layer implements services that receive user
requests, maps them to MDX query patterns, queries the server using Adomd.ne
t
and specifying an XML/A response type, transforms the XML/A to match the
request (table, indicator, chart) and returns it to the UI layer.
Consider that a standard XML/A response will usually include tons of
unnecessary metadata (in a custom app), so you can substantially reduce the
size of the XML/A message before sending it to the UI layer.
HTH,
--
Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html
"Ray" wrote:

> Hi,
> Thank you for the response. I have looked a little bit into Reporting
> Services... I may be a bit weak on the Sharepoint side of things though --
> my concern is that the RS2005/Sharepoint didn't offer sufficient
> customization abilities. Some things our Web App may need to do:
> - controls for boston matrix, various types of chargs, speedometers
> - full user session management, security, roles, etc.
> - integration with other data sources (e.g. Weather, newsfeed)
> - support for user input -- e.g. in cost/budget/forecasting analysis
> - support for "advanced" reports -- our organization is proficient with
> Crystal Reports, so we may wish to continue using it over RS2005.
> I was concerned that the solution/portal offered by Microsoft (or other
> vendors) would not meet our requirements. This is why I think it would mak
e
> sense if we built our own custom portal using our own technologies.
> Hence, my question about the middle-tier -- What type of data constructs
> should I be using?
> Thank you,
> Ray
> <Myles.Matheson@.gmail.com> wrote in message
> news:1124414479.649578.162120@.g49g2000cwa.googlegroups.com...
>
>

newbie - defining dimensions.

I'm new to datawarehouse and needed some assistance in designing the cubes. Below is the scenario.

I have two tables.

tblbook with columns (bookid, author)

tblauthor with columns (authorid, authorname)

Now, my report should include the no. of books authored by an author and also include details of co-authors.

i.e. tblbook has the following values

1 mike

1 joe

2 mike.

3 joe

4 richard

my result should display

mike 2, joe 1

joe 2

richard 1

I would really appreciate any help.

You may have over simplified your example a little bit. In order to properly model this sort of situation I would have thought you would have needed at least 3 tables.

tblBook(bookid, book_name, publishing_date, ...)

tblAuthor(authorid,author_name, ...)

tblBookAuthors(bookid, authorid)

Basically it sounds like you need to set up a many to many relationship (one book can have many authors and one author can have many books)

There is an excellent whitepaper on some of the uses of many to many relationships here: http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/7/Default.aspx

And you should be able to find information in Books Online.

|||

Thanks Darren, i will dig more into the article and update you for any issues.