Question about normalization

I have a question about normalization
basically I have an excel file with the following columns that my boss wants to store in a small database.

Brand Name, Retail Store, Location Info

Each Retail store may have one or more address. Each Retail store handles one or more brands Some brands may NOT be available in some Locations

So basically I created the following entities.

Brand (BrandID, Brand) Company (CompanyID, Name, Type (retail, manufacturer, etc)) Cmp_Location (LocationID, Address info..., CompanyID) Location_Brand (LocationID, BrandID)

What do you guys think?

You've laid out a decent start.

I wonder if, however, it might be advantageous to Consider that a Brand may have multiple Products. Perhaps a BrandProduct table will prove useful. (And then of course, there may eventually be a need for a BrandProductsDetail table.)

The Retail Stores carry Products, and they may not carry ALL Products for a Brand.

As a minor point, I would name the Locations table something like: CompanyLocations -it will sort following the Company table in the event your project continues to grow and develop a need for more tables.

(And with large retail operations, Brand is just a sub-component of a Supplier. One Supplier may control multiple Brands.)

If you are using SQL 2005. refer to Books Online about the use of schemas. You could have a schema for Suppliers, one for Retailers, etc. That would make it both easier to use Table names that are meaningful without being complex AND keep them located together. Consider

Suppliers.Companys Suppliers.Products Suppliers.ProductDetails Suppliers.Locations Retailers.Companys Retailers.Locations Retailers.Products

Question about Joins

Hi,

I have a stored Procedure called rpt_FundLipperRatings and i want to display a image which is store in the Database which a particular fundid doesnt exist. Below is the stored procedure that exist, I tried putting a case statement like totalreturnScore = CASE When TotalReturnScore IS NULL then 0 else TotalreturnScore END, but it doesnt work if the the fund doesnt exist in the database. So can some one please tell me how can i do it. and is it possible to put a where statement in a inner join

Like

Select * from fund f

Inner Join LipperLeaderPix ll on f.TotalReturnScore = ll.score where Condition

inner join .....

ALTER

PROCEDURE [dbo].[rpt_FundLipperLeaderRatings]

@.FundId

int

AS

SELECT

NumberOfYears

,

TotalReturnScore,

ll1.OverallBW TotalReturnImage,

ConsistentReturnScore

ll2.OverallBW ConsistentReturnImage,

PreservationScore,

ll3.OverallBW PreservationImage,

ExpenseScore,

ll4.OverallBW ExpenseImage

FROM

FundLipperLeader f

INNERJOIN LipperLeaderPix ll1ON f.TotalReturnScore= ll1.ScoreINNERJOIN LipperLeaderPix ll2ON f.ConsistentReturnScore= ll2.ScoreINNERJOIN LipperLeaderPix ll3ON f.PreservationScore= ll3.ScoreINNERJOIN LipperLeaderPix ll4ON f.ExpenseScore= ll4.Score

WHERE

f

.FundId= @.FundId

There are a number of ways you could handle this but I would have to see sample data in your tables to understand it.

You can always try using a LEFT OUTER JOIN instead of INNER JOIN on the two tables if you still want to return records that have NULL on the joined field

|||

Rossoneri,

Thanks for your reply and sorry for replying late.

The Fund Lipper Leader has the following Columns - FundId,,Numberof Years(0,3,5,10), DataValidDate, TotalReturnScore, ConsistentreturnScore, PreservationScore, ExpenseScore, addDate, LastChangeDate

All the score have data ranging from 0 - 5 and each score has a different picture associated with it.

The LipperLeaderPix has Columns which store Binary data(or the pictures) the columns are Score(0,1,2,3,4,5) OverallColor, ConsistentReturnColor, ExpenseColor, PreservationColor, TaxEfficineyColor, TotalReturnColor and OverallBW.

So the score is been associated with each picture. Is this information helpful or do u need more.

Regards,

Karen

|||

Rossoneri,

I tried running it using Left Outer Join but it didnt tried doing some like for some other sproc but i had to write a union to get to handle the non existent funds, but couldnt do it for this sproc.

Regards,

Karen

Question about how to upload file into database(sqlserver express)?

I'm a newbie.

I have googled many web sites,but i can't find the help that how to upload or store files(include images) into a database with Asp.Net 2.0.

Although i have found few samples about storing images into database,but the sample applied with Asp.Net 1.0 or 1.1 technology...

In microsoft MSDN,i found the help about FileUpload control,but the sample is about how to upload files to servers's folder with saveas() method.

Who can help me ?Give me a refer sample about upload files to database with Asp.Net 2.0. Is it need to use stream or what other method?

By the way, i want make a document managment system with vwd and sql express.

Thank u very much if u can give me a help.

The FileUpload control has a FileBytes property that has the raw bytes of a file. You want to save that data to an image column in the database.

I'm not a big fan of storing documents in a database. It wasn't designed for that, and while it can work, it doesn't work well. You're probably better off using the filesystem for the files, while storing the information about the files in the database.

Incidentally, if you're storing important stuff (legal documents, medical records, stuff like that), and you're not keeping the originals, you're asking for trouble.

|||

"you're not keeping the originals, you're asking for trouble."

why?

question about excute store procedure

Hi, all

i create a function("changefilepermission") to execute a procedure ("grant_file_access") to change the file permission. i click the permission checkbox to change new permission, after that i will click the submit button to update change to the database. but it does not change to the database. this is my part of code. is anybody can give me a help?

thanks in advanced!!!!!!!!!

Private Sub btnsubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click
 Dim FileTitle As New String("")
 Dim FileName As New String("")
 FileName = DGPermission.Items.Item(0).Cells(0).Text 'retrieve filename from datagrid
 FileTitle = DatabaseCommand(userid, "fa_title", filename) ' retrieve the filetitle from table
 Dim permission As Char ' set the permission value
 If (CkRead.Checked) Then
 Permission = "r"
 ElseIf (CkWrite.Checked) Then
 Permission = "w"
 ElseIf (CkExecute.Checked) Then
 Permission = "o"
 End If
 Try
 ' call the store procedure function by passing 4 value
 ChangeFileAccess(userid, FileName, FileTitle, Permission) '
 Catch ex As Exception
 lblErrorMsg.Text = ex.ToString
 End Try
End Sub
' execute store procedure function
Public Sub ChangeFileAccess(ByVal userid As String, ByVal DiskFilename As String, ByVal Title As String, ByVal Access As Char)
 Dim UpdateCommand As SqlCommand
 UpdateCommand = New SqlCommand
 With UpdateCommand
 .Connection = SqlConnection
 .CommandType = CommandType.StoredProcedure
 .CommandText = "Grant_File_Access"
 .Parameters.Add("@.vu_id", SqlDbType.VarChar, 20).Value = userid
 .Parameters.Add("@.DiskFilename", SqlDbType.VarChar, 64).Value = DiskFilename
 .Parameters.Add("@.Title", SqlDbType.VarChar, 50).Value = Title
 .Parameters.Add("@.Access", SqlDbType.Char, 1).Value = Access
 End With
 Try
 UpdateCommand.Connection.Open()
 UpdateCommand.ExecuteReader() ' call the store procedure
 UpdateCommand.Connection.Close()
 Catch ex As Exception
 lblErrorMsg.Text = ex.ToString
 End Try
 End Sub

What does your stored procedure look like?

Terri

Question about error handler

In store procedure, when a statement has error and Severity is high, so eror
is occured and stop the stored procedure and exit the sp. So I cannot use
@.@.error to catch the error. Is there any way that I can proceed the sp and
don't let it stoped so that I can use @.@.error to catch the error and make th
e
sp much nicely. Thanks.I am afraid that the answer is "no". You will have to capture the error at
the client side. See if this helps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Iter" wrote:

> In store procedure, when a statement has error and Severity is high, so er
or
> is occured and stop the stored procedure and exit the sp. So I cannot use
> @.@.error to catch the error. Is there any way that I can proceed the sp and
> don't let it stoped so that I can use @.@.error to catch the error and make
the
> sp much nicely. Thanks.
>

question about designing tables

Hi,

I want to define a table that has "order details" about one order where I can store a variable length list of prodcuts ordered in one order:

[prodID][qty][price]

That is to attch a collection of products to one order entry.

Thank you,
shlomi711Then you should create a table that looks like this:

(RowID Integer Identity) - Not really necessary, but I like to have unique row number
OrderID Integer - The order number (to which the details are connected)
prodID Integer - The product ID
qty money - the amount of products
price money - the price of the products.

Regards
Fredrik

Question about data type sqlserver ?

I have two problem :

+ The first, This is table store all items in bookshop system

tblItems:
IDItem Identity(Auto number)
Namebook nvarchar2
Price nvarchar2
Chapters nvarchar2
Weight nvarchar2 (weight of book)
....
I design data type for Chapter,or Price,Weight is nvarchar2 ? <--I wrong ? (I want to refer to principle of design the database)

+ The second ,When i design Price is the int datatype ! The default value is 0 ( I don't want to have this value ,i want to it is a empty field )

I really sorry because i ask too much ! Because i am a new programming !

Any Help or Advice would like appreciately ! Thanks u !

In my opinion you should use these datatypes:
IDItem Identity(Auto number)
Namebook nvarchar2
Price decimal
Chapters integer
Weight decimal orinteger depending upon the unit of measure
I don't understand what you are saying about Price and the integerdatatype and having a default value of 0. Unless you do somethingspecial SQL Server will insert a NULL into that field if you haven'tspecified a value.

|||

Thank tomorton very much !
I really don't understand what happen to me !

But I design again table ,everything is good !

question about best way to store an up or down value

I'm creating a table for maintenance records.

In each record many of the values are simply checkboxes.

In the database for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true?

-DavidWithout getting into design issues, the best way would be to use a BIT datatype, with 0 used to indicate FALSE or OFF, and 1 to indicate TRUE or ON.

Question about "The inside of the 'UDF' function"

Hi

I know that when i call the store procedure in the sql server 2000, it will take a long time.
But after, if i call the store procedure again, it will run faster than before.

As i know:
Stored procedures are more efficient in part because the procedure is stored in SQL Server when it is created. Therefore, the content in the procedure runs at the server when the stored procedure is executed. A complex Transact-SQL script contained in a stored procedure is called by a single Transact-SQL statement, rather than by sending hundreds of commands over the network.

Before a stored procedure is created, the command syntax is checked for accuracy. If no errors are returned, the procedure's name is stored in the SysObjects table and the procedure's text is stored in the SysComments table. The first time the stored procedure is run, an execution plan is created and the stored procedure is compiled. Subsequent processing of the compiled stored procedure is faster because SQL Server does not recheck command syntax, re-create an execution plan, or recompile the procedure. The cache is checked first for an execution plan before a new plan is created.

My question is that whether the UDF function is the same as the store procedure ?

Can you please narrow down your question a bit. What product are you trying to use? What version of the product?

Is this question for SQL Server Analysis Services?

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward Melomed:

sql 2000 any version

Not for Server Analysis Services

And i found the answer on www.sql-server-performance.com

That's the answer i think:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////

Question
I work in a SQL Server 2000 environment where we have literally hundreds of stored procedures in one database that are utilized for an OLAP application, and likewise for an OLTP application.

I have seen in many instances where a stored procedure is used solely because one can pass parameters to it and use these parameters in the WHERE portion of a SELECT clause. These stored procedures do nothing more than return a result set.

I was also told that SQL Server has a FIFO queue where only a certain number of pre-compiled stored procedures reside. Furthermore, I was also told that I should consider using user-defined functions that does its SELECT from a view(s) and uses any parameters passed in as delimiters in the WHERE portion of the SELECT clause.

Since functions are not pre-compiled but stored procedures are, is there any performance gain from using user-defined functions with views as opposed to stored procedures?

Answer
Before SQL Server 2000, user-defined functions were unavailable. Because of this, stored procedures were often the only way to emulate what a user-defined function can do now. So the question becomes, are there any advantages of converting my current stored procedures to user-defined functions?

I want to break the answer for this question into two parts: performance and convenience issues. Let's start with performance first.

For the most part, rewriting stored procedures as functions will not give you any performance benefits, and quite possibly, they may cause a performance hit because of the extra overhead they incur as compared to stored procedures. The amount of the performance hit, if any, will depend on how the function is written and what it is doing. Keep in mind that user-defined functions are also pre-optimized and compiled similarly to stored procedures (unlike what you have been told). Even so, they have more overhead than corresponding stored procedures.

From a convenience standpoint, there are some reasons to rewrite some stored procedures as user-defined functions, assuming performance is not a major issue. Some of these include:

The ability for a user-defined function to act like a table gives developers the ability to break out complex logic into shorter code blocks. This will generally provides the additional benefit of making the code less complex, and easier to write and maintain.

If you want to be able to invoke a stored procedure directly from within a query, then rewriting a stored procedure as a user-defined function would be worthwhile.