Ecommerce Database Design

First, is there a forum here for advanced database design that I'm missing?

I have an ecommerce project I'm working on for which I have to develop a database that will house many products under many categories with unlimited subcategories/levels. For example:

baseball
baseball \ weightlifting
baseball \ weightlifting \ upper body
baseball \ weightlifting \ lower body \ ...

The levels of subcategories must be completely scalable, as the administrator wants to be able to add and subtract at will. Further, there is the idea that some subcategories will transfer across other categories (ex: weightlifting would be a subcategory of multiple sports).

In previous database designs I'vesimply have a category table and maybe a subcategory table; obviously that won't work here, as the number of levels of categories is unknown.

Does anybody have any suggestions, examples, recommended books, forums, etc. to point me in the right direction?

Any help would be greatly appreciated.

BrianOk, Brian... I have designed simmilar type of the databases... the most important thing U should remember is that U deal with relational database instead of treewise... so U must represent the data as a couple of relations (in other words, tables) and link them in Ur queries.
So... for example... place all the categories U have into the table Categories (CatID, Name) and create the table... Relations (RelID, CatID, ParentCatID). So... the Categories should contain the data:

CatID | Name
____________
1 | baseball
2 | weightlifting
3 | upper body
4 | lower body

And the Relations would be:

RelID | CatID | ParentCatID
________________________
1 | 1 | 0 -- that means: "no parent category".
2 | 2 | 1
3 | 3 | 2
4 | 4 | 2

and so on...|||Thanks very much for the suggestion.

I was thinking about having the parentID in the category table. For example:

CatID | ParentID | CatName
-----------
1 0 Baseball
2 1 Weightlifting

...

Is there some advantage to having a separate table for the relations that I'm missing?|||oh... U can for shure ... even to say there is redundant data in my case... yes. If wonna discuss anything more... in more rapid mode... here u r : ICQ# 303978464.

Easy question for experienced developers

How do I add a previously-made SQL Compact Edition database to my Visual Basic.Net 2005 project inside Visual Studio, and ensure that it gets built into the ClickOnce-deployed solution? Our dba has completed the db design for our project, and I need to add the file to the project I am writing business logic for.

I've been writing software for about 15 years but just now using SQL CE for the first time. I hope my questions are not too simplistic for this forum, but I ran into a problem with something so simple that I'm at a loss, and I thought I'd throw it in this newbie thread.

To wit, what is wrong with this syntax:

INSERT INTO refState(StateName)
VALUES('Alberta')
INSERT INTO refState(StateName)
VALUES('Alaska')

This is a snippet from a T-SQL script I've been using for years that populates reference tables for things like states, countries, etc.

When I try to run the query in Visual Studio 2005 Pro while I have my refState table open in the IDE, I receive the following error:

'Unable to parse query text'.

Then I say to myself, What? Why? So I try to verify the SQL syntax, and then I receive this new error:

'This command is not supported by this provider.'

On the surface it looks like ANSI SQL is out the window. So, I'm guessing I'm missing some fundamental understanding.

Little help? thanx

|||

Well, consider that the Compact Edition is not SQL Server and does not understand TSQL. The problem with your SQL is that the SQLCe engine can process one (and only one) SQL statement at a time. It does not understand the concept of multiple operations or resultsets as supported in TSQL.

In my EBook, I show an example (and a class) used to import schema files that can be used to create a SQLCe database and populate the schema. Gettting data into the database is also a challenge as SSIS does not work well with SQLCe (yet). One approach is to setup Merge Replication with a SQL Server Workgroup (or better) engine, use RDL or the new (unreleased) ADO Synchronization Services. Sure, you can write your own INSERT loop to import data and this would not be that hard to do... code-intensive but doable.

hth

See www.hitchhikerguides.net

|||

Thx for the feedback Bill.

Also, I found what we needed as far as setting up private ClickOnce deployments (which is what we're using) at the following URL:

http://msdn2.microsoft.com/en-us/library/bb219482.aspx

Dynamically name folders

Apologies in advance for the newbie question. I'm new to Integration Services and am trying to build my first project.

I want to dynamically create folders based on the results of a stored procedure. The resultset is limited to one column but can contain multiple rows. (It returns a list of 8-character strings representing dates.) This returned value needs to be combined with the path where the new folders should be created.

Can someone point me in the right direction? I've run the stored procedure and put the resultset into a RecordSet Destination that uses a variable of type Object. (At least, I think I've done it correctly.) But I have not been able to figure out how to access this data to actually create the folders.

Thanks for the help,

Chris

Use a foreach loop.

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx|||

Thanks!!!

That was exactly what I needed and worked like a charm.

Chris

Dynamically change the servername

Hi all,

I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).

For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.

Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.

My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.

I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.

How can I achieve this the easiest way ?

Thanks in advance !

Geert

You need to take a look at creating "package configurations". Try doing a search in Books Online for this. It helps you to do exactly what you are looking for.

Dynamically Adding DataFields to Chart

I am in need of some advice on my first project.
I have a chart which displays as I would expect when a static query is
executed. I need to somehow dynamically add the datafields to the chart at
runtime which is where I am coming unstuck. The number and name of
datafields could change each time the report is run so they absolutely need
to be dynamic.
Any advice on the best way to approach thsi problem would be much
appreciated.
Thanks, SimonDoes anyone have experience in creating Reports with Dynamic fields like
this. The only solution I can think of is to programatically build the RDL
each time, save it to the server then call the report to render it for the
client. It seems very inefficient which is why I wanted to ask others with
more experience in this field '?
Simon
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote
> I am in need of some advice on my first project.
> I have a chart which displays as I would expect when a static query is
> executed. I need to somehow dynamically add the datafields to the chart at
> runtime which is where I am coming unstuck. The number and name of
> datafields could change each time the report is run so they absolutely
need
> to be dynamic.
> Any advice on the best way to approach thsi problem would be much
> appreciated.
> Thanks, Simon
>

Dynamic SQL Syntax H_ll Please Help

This forum has helped me get to this point a couple times with this project BUT I just can't seem to get the syntax correct. Either one of these two statements do exactly what I want them to do:
SELECT @.RtnValue = Column0 FROM MyTable WHERE RowIndex = @.RIndex
SELECT @.RtnValue = (SELECT Column0 FROM MyTable WHERE RowIndex = @.RIndex)

@.RtnValue is the value the program will work with.

The only problem is that Column0 and @.RIndex need to be dynamic so I can index through each Column and Row of the table.

This is the code I am trying to use to do this dynamically, naturally it will be in two while loop to index through each Column and Row

DECLARE @.RtnValue smallint
SET @.RtnValue = 0

DECLARE @.RIndex smallint
SET @.RIndex = 20

DECLARE @.ColumnName varchar(10)
SET @.ColumnName = 'Column0'

DECLARE @.MySelectString varchar(200)
SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + @.RIndex + ' )'
--SET @.MySelectString1 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = 1 )'

EXEC( @.MySelectString0 )
--EXEC( @.MySelectString1 )

@.MySelectString0 produces this error:
Server: Msg 245, Level 16, State 1, Line 23
Syntax error converting the varchar value 'SELECT @.RtnValue = ( SELECT Column0 FROM MyTable WHERE RowIndex = ' to a column of data type smallint.

@.MySelectString1 produces this error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable'@.RtnValue'.

I have tried many different combinations of syntax but can not seem to get the magic combination. Can someone tell me the correct syntax to get this to work.

Thank you in advance.

try something like this :

SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + CONVERT(VARCHAR,@.RIndex) + ' )'

|||

Thank You for your responce I will try your solution. I sure hope it works I have been stuck on this problem to long Thanks again

dynamic SQL stored procedure problem

Hi,
I have a project that all its reports are based on Dynamic SQL Stored Procedure. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied." Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.
When I wrote in the data set query "exec x @.y=1" and chose "Text" instead of "Stored Procedure" I got the correct SP answer, but because all the reports get their parameters from the application (user) I can not leave the report like that.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comYou should be able to do this:
exec x @.y and then map the query parameter to the report parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Alot of times this will happen when you do not have the parameter defined in
the dataset. RS takes your parameters but ends up not passing them to the
stored proc because you haven't explicitly told it to in the properites of
the dataset. Check it by going to the appropriate dataset. hit the elipsis
(...)-> parameters tab. You need to list the parameters that the proc takes
in the correct order that it takes them. The name value list would look as
such
NAME VALUE
@.x =Parameters!X.value
@.y = Parameters!Y.Value
See if that helps!
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Dynamic SQL Stored Procedure problem

Hi,
I have a project that all its reports are based on Dynamic SQL Stored
Procedure. First, I had to handle the problem that in the Layout Tab
there were no fields by adding them manually. However, when I try to
show the report in the Preview tab I got the error: "Procedure x
Expects a parameter @.y that was not Supplied" Despite the fact that I
define the parameter in report parameter and in the Data Tab I got the
correct SP answer.
When I wrote in the data set query "exec x @.y=1" and chose "Text"
instead of "Stored Procedure" I got the correct SP answer, but
because all the reports get their parameters from the application
(user) I can not leave the report like that.
Any Ideas?
Thank you.
AviI've had this problem a few times, and ended up solving it by simply
shutting down and restarting Visual Studio (after saving the report, of
course). Not sure if it will work for your problem, but its worth a
try if you haven't done that already.
AB wrote:
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab
> there were no fields by adding them manually. However, when I try to
> show the report in the Preview tab I got the error: "Procedure x
> Expects a parameter @.y that was not Supplied" Despite the fact that I
> define the parameter in report parameter and in the Data Tab I got the
> correct SP answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but
> because all the reports get their parameters from the application
> (user) I can not leave the report like that.
> Any Ideas?
> Thank you.
> Avi

Dynamic SQL Stored Precedure problem

Hi,

I have a project that all its reports are based on Dynamic SQL Stored Procedures. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied."Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.

When I wrote in the data setquery"execx @.y=1"and chose "Text" instead of"Stored Procedure"I got thecorrect SP answer, but because all the reports gettheir parameters from the application (user)I can not leave the report like that.

Try this: As a data set write static query that returns all the fields of your dynamic query and uses all the parameters. Then refresh report so it will update parameter list and field names. After that you can change static query to dynamic.

I did it like this:

declare @.sql varchar(max); set @.sql = '';

set @.sql = '.....'

exec (@.sql)

Maciej