Database level lock: store

Showing posts with label store. Show all posts

Thursday, March 29, 2012

Database images not showing

How should I store an image into my SQL database in order to be able to read
it using Reporting Services Image control? I have a table with an image
field (datatype of the field is 'Image').
I have tried several ways and the report is always rendered with the image
showing a red cross inside it (image broken). However if I read the table
using MS Access ADP and double click on the image field, MS Paint opens and
shows up the image (this is a bmp image). I have also set the MIMEType of
the rs image field to 'image/bmp' (filling this field this is compulsory
with database images).
It seems that, even though the database field contents ('Image' field) are
properly stored, Reporting Services does not know how to handle it. May I
need to do a CONVERT(Binary, MyImageField) or the database field be of
another type? How should I store the image inside the database so that RS
could read it?
Regards.
PS: This is RS SP2.This sounds like the images are stored as OLE images in the database (e.g.
Access would convert images into OLE images). You can try the following
expression to get rid of the OLE chunk:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
> How should I store an image into my SQL database in order to be able to
> read it using Reporting Services Image control? I have a table with an
> image field (datatype of the field is 'Image').
> I have tried several ways and the report is always rendered with the image
> showing a red cross inside it (image broken). However if I read the table
> using MS Access ADP and double click on the image field, MS Paint opens
> and shows up the image (this is a bmp image). I have also set the MIMEType
> of the rs image field to 'image/bmp' (filling this field this is
> compulsory with database images).
> It seems that, even though the database field contents ('Image' field) are
> properly stored, Reporting Services does not know how to handle it. May I
> need to do a CONVERT(Binary, MyImageField) or the database field be of
> another type? How should I store the image inside the database so that RS
> could read it?
> Regards.
> PS: This is RS SP2.
>|||Thanks for your reply but it seems that there should be another reason. Your
explanation is on the good road, but
I have tried your expression, with that 105 varying from 100 to 110 with the
same results. The red cross is still there.
Any other suggestion? Regards.
PS: Just for further debugging, I have an output of the first 950 bytes
generated by the expression:
=System.Convert.ToBase64String(Fields!Picture.Value)
Here they follow:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
> This sounds like the images are stored as OLE images in the database (e.g.
> Access would convert images into OLE images). You can try the following
> expression to get rid of the OLE chunk:
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
> 105))
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read the
>> table using MS Access ADP and double click on the image field, MS Paint
>> opens and shows up the image (this is a bmp image). I have also set the
>> MIMEType of the rs image field to 'image/bmp' (filling this field this is
>> compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field be
>> of another type? How should I store the image inside the database so that
>> RS could read it?
>> Regards.
>> PS: This is RS SP2.
>|||More information on the subject:
I have been doing more tests with this sample bitmap. I have sent it to
myself via email just to read the source code of the message and extract the
base64 of the bitmap. The header of attachment and some hundreds the
begining bytes are here:
--_=_NextPart_001_01C55532.6E34D77F
Content-Type: image/bmp;
name="PHOTO.BMP"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="PHOTO.BMP"
Qk1gSAAAAAAAAHYAAAAoAAAAzwAAALEAAAABAAQAAAAAAAAAAAAQFwAAEBcAAAAAAAAAAAAAAAAA
AP///wD6+voA8/PzAOfn5wDb29sAzMzMALm5uQCjo6MAi4uLAHNzcwBZWVkAQkJCAC8vLwAfHx8A
Dw8PABERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERARERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
etc...
If I copy this base64 chunk of data and set the Reporting Services image
field to:
=System.Convert.FromBase64String("the chunk")
The image is shown perfecly on the report.
However, if I compare some bytes of the begining of this chunk (which is
shown to be correct) and try to find it somewhere on the string returned by:
=System.Convert.ToBase64String(Fields!Picture.Value)
which is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
I can't find it anywhere. There is no 'Qk1gS' substring in it. Now my
question are: Are there multiple ways of converting a file into a base64
string? How can I remove the OLE header of the database if I cannot find the
matching of the image REAL data using this 'comparing' approach?
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>|||Instead of doing tests with the final image, I have created a 5x5 pix bitmap
to work/test with.
The Base64 encoding of it (grabbed from an email sourcecode is):
Qk2GAAAAAAAAADYAAAAoAAAABQAAAAUAAAABABgAAAAAAFAAAAAAAAAAAAAAAAAAAAAAAAAA////////////////////AP///wAA/////////wAA/wD///////////////////8A////////AAD/////AAD/AAAA/////////////////wA=
It works perfectly if I set the value property of the rs image to:
=System.Convert.FromBase64String("the former base64 string")
Now, the database version of the same file: I set a texbox in the RS report
and set its value to =System.Convert.ToBase64String(Fields!Picture.Value) so
that I could retrieve the complete base64 of the image stored in the
database. Here it is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAoAAAAEJNhgAAAAAAAAA2AAAAKAAAAAUAAAAFAAAAAQAYAAAAAABQAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////////wD///8AAP////////8AAP8A////////////////////AP///////wAA/////wAA/wAAAP////////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBQAAAAAAAIqtBf4=
By other means, I have been able to revert this base64 string back to binary
and here are the results:
9 + ÿÿÿÿImagen de mapa de bits Paint.Picture PBrush
BM? 6 ( P ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿ
ÿÿÿÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿ ÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿ
S þ
Of course, I have some rubbish here. But not everything is lost: Now I can
see that a localized language string is included in the OLE chunk before the
'BM' indicating the begining of the bitmap file. And now the final question:
Should I use other string than
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
when the locale of the systems/servers/software is not english? In my case
I'm using spanish and since the string 'Imagen de mapa de bits' is not as
long as 'Bitmap image file', I think that 105 is not valid/correct when
other languages are used.
After some calculations I think that the OLE chunk size for spanish (which
is my case) is 121 instead of 105. However the red cross is still there!!!!!
:(((
Another aproach, instead of letting RS cropping the image ole data, is to
retrieve the image from the database without it. Is it feasible to retrieve
just a 'substring' of the image?
Please help. I really need it. Thaks.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>

Database Idea

Good morning

In messages system i have table to store messages and another table to contain the links which the message is posted through

Now i want to delete a link but i don't want to delete the message sent through this link

The problem:

When displaying the message sent what will be fount in the link field (i.e for the deleted link)

I tried to move the deleted link data to separate table (EX: deletedLink) but if the user added new link with the same name as the deleted link?The problem mainly in when displaying the message sent i will have the same link twice one for the deleted and the other for the added one.

If any one has a good idea for doing that please reply to me

I don’t want the sql code

I want just the idea

Thanks

kind regards

Mohammed Al Maghraby

Hey,

I posted in your other forum post.

Sunday, March 25, 2012

Database for storing Polish characters

Hi!
We have apps running on a English-based MS SQL Server 2000. One of our
clients
has asked about if it is possible to store Polish characters in database
whose collation
is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
their MS SQL Server 2000
server using the Collation for Polish language.
How about the data types used in tables ? we do not use UNICODE data
types such as nvarchar, etc.
Would it become a problem for storing polish characters into SQL Server
2000 which was installed with
collation for Polish ?
Regards,
-- Peter LoPeter,
From the SQL BOL:
"You can specify collations for each character string column using the
COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
Another option if you're not using UNICODE data types.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi!
> We have apps running on a English-based MS SQL Server 2000. One of our
> clients
> has asked about if it is possible to store Polish characters in database
> whose collation
> is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
> their MS SQL Server 2000
> server using the Collation for Polish language.
> How about the data types used in tables ? we do not use UNICODE data
> types such as nvarchar, etc.
> Would it become a problem for storing polish characters into SQL Server
> 2000 which was installed with
> collation for Polish ?
>
> Regards,
> -- Peter Lo
>
>
>|||Thanks for quick response.
So, to be able to store Polish characters in MS SQL Server 2000, I have
two options:
Option #1:
1. install database with SQL_Latin1_General_CP1_CS_AS
2. As you suggested, use ALTER TABLE to set all string-columns to use
the
UNICODE data type
Option #2:
1. Install database with collation for Polish language
2. All string columns in tables must use UNICODE data types.
Please confirm. Thanks
-- Peter Lo
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Peter,
> From the SQL BOL:
> "You can specify collations for each character string column using the
> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
> Another option if you're not using UNICODE data types.
> HTH
> Jerry
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> > Hi!
> >
> > We have apps running on a English-based MS SQL Server 2000. One of our
> > clients
> > has asked about if it is possible to store Polish characters in
database
> > whose collation
> > is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to
install
> > their MS SQL Server 2000
> > server using the Collation for Polish language.
> >
> > How about the data types used in tables ? we do not use UNICODE data
> > types such as nvarchar, etc.
> > Would it become a problem for storing polish characters into SQL
Server
> > 2000 which was installed with
> > collation for Polish ?
> >
> >
> > Regards,
> >
> > -- Peter Lo
> >
> >
> >
> >
> >
> >
>|||Peter,
You can use a UNICODE data type, a column level collation or a database
level collation. Choice would probably be dependent on the number of
columns and tables that will be storing Polish characters.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%238NEGCvuFHA.252@.TK2MSFTNGP09.phx.gbl...
> Thanks for quick response.
> So, to be able to store Polish characters in MS SQL Server 2000, I have
> two options:
> Option #1:
> 1. install database with SQL_Latin1_General_CP1_CS_AS
> 2. As you suggested, use ALTER TABLE to set all string-columns to use
> the
> UNICODE data type
> Option #2:
> 1. Install database with collation for Polish language
> 2. All string columns in tables must use UNICODE data types.
> Please confirm. Thanks
>
> -- Peter Lo
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
>> Peter,
>> From the SQL BOL:
>> "You can specify collations for each character string column using the
>> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
>> Another option if you're not using UNICODE data types.
>> HTH
>> Jerry
>> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
>> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> > Hi!
>> >
>> > We have apps running on a English-based MS SQL Server 2000. One of
>> > our
>> > clients
>> > has asked about if it is possible to store Polish characters in
> database
>> > whose collation
>> > is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to
> install
>> > their MS SQL Server 2000
>> > server using the Collation for Polish language.
>> >
>> > How about the data types used in tables ? we do not use UNICODE data
>> > types such as nvarchar, etc.
>> > Would it become a problem for storing polish characters into SQL
> Server
>> > 2000 which was installed with
>> > collation for Polish ?
>> >
>> >
>> > Regards,
>> >
>> > -- Peter Lo
>> >
>> >
>> >
>> >
>> >
>> >
>>
>

Wednesday, March 7, 2012

database design:dnamically build where clause

Hi
I am creating a table to store metadata information for certain technical
parameters which are stored in various tables.
These parameters have various criteria to determine if they are compliant to
certain predefined conditions or not.I will explain with an example:
For ex,
paramA is compliant if its value is 50
paramB is compliant if its value is between 0 and 2
paramC is compliant if its value is 35% of another column
paramD is complaint depending on colA,ColB,ColC
If colA = 5 then paramD is compliant if paramD=2.
If colB = 10 then paramD is compliant if paramD=3
If colC =50 then paramD is complaint if paramD=70
paramE is compliant if is 1 or 0
So my table has fields such as
DDId,
ParamName
ParamDBname
ParamTableName
Compliancecondition(varchar 200)
etc.
I need to store the information regarding these compliant conditions so that
I can build my queries like
select <paramName>,case when<compliance condition is true> then 1 else 0,
from <paramDB>.<paramTable>
where<compliancecondition>
but this will result in dynamic SQL.is there a better way to store the
information for these varying compliance conditions?
thanks for your help.
etcLooks like you need something like a rules engine to satisfy your dynamic
needs. Check this nice article on this subject to see if its useful for your
scenario:
http://msdn.microsoft.com/sql/defau.../>
eengine.asp
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"

tech77" <

tech77@.discussions.microsoft.com> wrote in message
news:7E249C36-147E-4A0A-9B77-42F9F4CA6E6E@.microsoft.com...
> Hi
> I am creating a table to store metadata information for certain technical
> parameters which are stored in various tables.
> These parameters have various criteria to determine if they are compliant
> to
> certain predefined conditions or not.I will explain with an example:
> For ex,
> paramA is compliant if its value is 50
> paramB is compliant if its value is between 0 and 2
> paramC is compliant if its value is 35% of another column
> paramD is complaint depending on colA,ColB,ColC
> If colA = 5 then paramD is compliant if
> paramD=2.
> If colB = 10 then paramD is compliant if
> paramD=3
> If colC =50 then paramD is complaint if
> paramD=70
> paramE is compliant if is 1 or 0
> So my table has fields such as
> DDId,
> ParamName
> ParamDBname
> ParamTableName
> Compliancecondition(varchar 200)
> etc.
> I need to store the information regarding these compliant conditions so
> that
> I can build my queries like
> select <paramName>,case when<compliance condition is true> then 1 else 0,
> from <paramDB>.<paramTable>
> where<compliancecondition>
> but this will result in dynamic SQL.is there a better way to store the
> information for these varying compliance conditions?
> thanks for your help.
>
>
>
> etc

Saturday, February 25, 2012

Database design question

I have a need to keep a bunch of data regarding some wan links, and I don't know how to design my database for it.
Here's the data I need to store.

I have about 35 different links, and about 50 different application volumes that need to be stored in this database, for every hour of the day.
Right now the way I have it setup doesn't seem too good, but i can't figure out any other way.
I currently have 1 different table for each link, and every application as a column in these tables.
So for example:
Table Link 1
Date App1 App2 App3 App4
Jan 1 01:00 54613 351546 6848435 6847684
Jan 1 02:00 668468 6846433 646464 6546846

Table Link 2
Date App1 App2 App3 App4
Jan1 01:00 6846 6844354 6846434 38463434
Jan1 02:00 648458 3848646 6846 684684

etc.

The primary key on each table is the date.
I'm only storing about 7 applications right now, but I need to be able to increase this to about 50, so it's gonna make for huge queries for reporting.
The thing is I need to be able to report on total application volumes across every link.
So that means right now i'm doing a Sum(Link1.App1) + Sum(Link2.App1).... which I have to do 35 times, and then repeat again for the next application. And most reports have a lot of applications required so it makes for queries that are 20 pages long...

Is there any design solution that would make this more efficient?
Thanks!

You need to supply more information. It sounds to me like you might need an APPLICATION table to describe each applaction and a LINK table to describe each specific link -- but without addtional information might change that. It also sounds like you need at least one more table to record your time-sensitive data; however, I don't clearly see this at the moment.

Like I said: More information is needed.

|||

Ok.

Basically, let's say I have 35 different wan links going across my network.

Going across those links are a bunch of different applications. Let's say there's 50 of them.

I have a system that records the volume generated by each application over everyone of those 35 links, every hour.

Now I need to be able to store this data long term.

So the only thing being stored really is the volume for each application.

Right now it's setup this way:

TableLink1

Date Citrix FTP Telnet HTTP ..................etc

Jan 1 01:00 839203 923849 239487 83823

The date/time is the primary key as there will never be two entries for the same date/time.

The numbers being stored under the application columns are bytes.

I will need to be able to report on a few different things. For example, I will have to be able to produce the Top 10 applications for a given time period.

So that will mean i'll have to add up every application for every link and determine which 10 volumes are highest. With 35 different tables right now, my query

is extremely long and I don't know if it's supposed to be like that.

There's also a requirement for reports on a specific application across all links. So for example, I'll need to show the total volume used by FTP across

the 35 links for a given time period.

With the design that i'm using right now, it's possible, but it's a long query which looks like:

SELECT SUM(TableLink1.Citrix) + SUM(TableLink2.Citrix) + SUM(TableLink3.Citrix) .....etc AS Citrix

I hope this is enough info...if not tell me what you require. I'm pretty sure my design is ok, but just want to confirm if there's no better way.

I was also considering a different design that would look like this:

ApplicationCitrix

Date Link1 Link2 Link3 ...... Link35

Jan 1 01:00 23487234 234234 23487 ...... 3248324

Basically instead of using a different table for each link, i'd use a different table for each application...but I think it comes back to the same thing.

|||

Probably create application table something like this:

tb_APPLICATION

ApplicationID tinyint identity(1,1)

ApplicationName nvarchar(255)

Link table something like this:

tb_LINK

LinkID tinyint identity(1,1)

LinkValue bigint

and finally a cross-reference table:

xRef_App_Link

ApplicationID (fk)

LinkID (fk)

LinkStartDate smalldatetime

LinkEndDate smalldatetime

|||

Umm...i'm not sure i'm quite following...

If you can explain a little more maybe.

I don't get how the tb_LINK table would work...

How can I store a LinkValue without associating it to a specific application...

The way i'm thinking is maybe this:

keep the same tb_APPLICATION

then the tb_LINK:

LinkID tinyint identity(1,)

LinkName nvarchar(255)

And then the cross reference table:

xRef_App_Link

ApplicationID (fk) (pk)

LinkID (fk) (pk)

Date (pk)

Volume

Would this work?

I didn't quite get the part where you wrote:

LinkStartDate smalldatetime

LinkEndDate smalldatetime

|||

I think you want the app table:

tb_APPLICATION

ApplicationID tinyint identity(1,1)

ApplicationName nvarchar(255)

I am not sure if you need a LINK table or whether all you need is a VOLUME table that contains something like

Volume

RowId identity

LinkId

ApplicationId

LinkVolume

LinkStartTime

LinkEndTime

|||

Ok,

I think I will do it this way instead of having 35 tables lol...

Thanks a lot for the help.

|||I did not mean my response as "the answer" and I think it is pre-mature to mark it as such. I was hoping to get other opinions. Other ideas?

Database Design Question

Question 1:

I have a contacts table which holds different types of contacts. I contact can only have 1 type. I have a contact types table and store the id of the contact type in the contacts table. I also have another table (table 1 for example) that has a 1 to many relationship with a contact. A contact can have 1 or many of table 1. But only a certain type of contact. Not all contact types have this relationship with table 1. They aren't related to table 1 at all. Is this ok, and how is it represented in a database diagram?

Question 2:

I have a contacts table and an address table and a contact can have 1 or many addresses. I also have a vendors table. They will also have addresses. I want to share the address table with contacts and vendors. Would I just have an address id as a primary key. Use the contact id or vendor id (depending on what type the address is for) as a foreign key, and then also have another column in the address table that held lets say a 0 or 1 depending on whether or not it was a contact address or a vendor address? Would this be the proper way to do this?

Any suggestions are welcome and thank you in advance!Question 1:
Is this ok, and how is it represented in a database diagram?most definitely yes, it is okay

diagram? diagram?

(you have to imagine jim mora saying "playoffs? (http://youtube.com/watch?v=5rmtO9_wzlI)")

is this a homework assignment?

Question 2:
Would I just have an address id as a primary key.yes

the other part is trickier

you could, for example, have both a contact id and a vendor id foreign key in the address, both of which must be nullable, since one of them (the one it isn't an address for ;)) will be null on every row

i personally would not have a 0 or 1 flag for whether or not it was a contact address or a vendor address|||lol...no..not a homework assignment. Just trying to be very organized on this project! Thanks for your reply. Would you recommend sharing the address table, or, would you have a separate address table for contact and vendor? I would think it makes sense to have only 1 address table.|||I would think it makes sense to have only 1 address table.okay, let's go with that for a second

why?|||why what...have a diagram, or have 1 address table?|||why does it "make sense" to have only one address table? how is it better than two?|||less duplication? Can I run another design by you? I'm really not doing homework...it's been a long time since I've done any database design so I'm rusty.

I have this situation.

A person can hold a season ticket for 1 or more teams. A team has more than 1 game and a game has more than 1 ticket. Here is how I'm designing my DB (not, only including necessary fields right now) Does it make sense to you?

Table:
tblTeam
Field(s):
TeamID PK

Table:
tblContacts
Field(s):
ContactID PK
(note: I will be using the contacts table for people other than season ticket holders as well)

Table:
tblSeasonTicket
Fields(s):
SeasonTicketID PK
TeamID FK
ContactID FK

Table:
tblGames
Field(s):
GameID PK

Table:
tblTickets
Field(s):
TicketID PK
SeasonTicketID FK
GameID FK

Appreciate your thoughts if you're willing to give them.|||less duplication? i don't think so

how often will a vendor and a contact share an address?|||sorry...what I meant was less tables...I'm going to have the same fields for an address. instead of creating a new table, I would only have to add a field. I actually prefer your way with the two tables...I find that easier to keep track of.|||you can bill me for it if you want|||no, my answers on public discussion forums are always free :)|||great...do you have a comment about my ticket scenario? :-)|||is it possible to get a ticket for a game that isn't a season ticket?|||no, it is not possible to get a ticket for a game that isn't a season ticket

Friday, February 24, 2012

Database design problem

I'm trying to built a database for ecommerce and I'm stuck with this problem.

I'll use a grocery store for the example.

First here's the tables.

table product_prd
id_prd
name_prd
description_prd

A product may have different "sizes" like a bag of chips. The table product_prd is linked (one-to-many) to product_size_pds

table product_size_pds
id_pds
idprd_pds //Foreign Key - product_prd.id_prd
name_pds //this contains the name of the size (ex. small,medium, large)
price_pds
weight_pds
lenght_pds
height_pds

I thinks there's a problem with my structure if the product as only one size like a camping chair.

I just can't leave the name_pds field empty or repeat the name from name_prd. It seems bad practice.

Is there a structure that could hold single size product and multiple size products?

Hope this makes sense.

Thanks

Quote:

Originally Posted by ZoeNet

I'm trying to built a database for ecommerce and I'm stuck with this problem.

I'll use a grocery store for the example.

First here's the tables.

table product_prd
id_prd
name_prd
description_prd

A product may have different "sizes" like a bag of chips. The table product_prd is linked (one-to-many) to product_size_pds

table product_size_pds
id_pds
idprd_pds //Foreign Key - product_prd.id_prd
name_pds //this contains the name of the size (ex. small,medium, large)
price_pds
weight_pds
lenght_pds
height_pds

I thinks there's a problem with my structure if the product as only one size like a camping chair.

I just can't leave the name_pds field empty or repeat the name from name_prd. It seems bad practice.

Is there a structure that could hold single size product and multiple size products?

Hope this makes sense.

Thanks

What you might need in your design is transform the product_size_pds table into a mapping table and define the product sizes in a separate table named size:

table product
id_prd //Primary Key (Identity)
name_prd
price_prd
description_prd

table map_product2size
id_prd2size //Primary Key (Identity)
id_prd //Foreign Key from product_prd table
id_size //Foreign Key from size table

table size
id_size //Primary Key (Identity)
name_size //this contains the name of the size (ex. small,medium, large)
weight_size
lenght_size
height_size|||

Quote:

Originally Posted by davef

What you might need in your design is transform the product_size_pds table into a mapping table and define the product sizes in a separate table named size:

table product
id_prd //Primary Key (Identity)
name_prd
price_prd
description_prd

table map_product2size
id_prd2size //Primary Key (Identity)
id_prd //Foreign Key from product_prd table
id_size //Foreign Key from size table

table size
id_size //Primary Key (Identity)
name_size //this contains the name of the size (ex. small,medium, large)
weight_size
lenght_size
height_size

If I take a single size product (the camping chair) with your structure, I still need to enter a size to the product witch I don't think is applicable for a single size product. And for the price it should be in the table size because the price varies with the different size of the product.|||

Quote:

Originally Posted by ZoeNet

If you define a size, can it be applied to different products? If yes, then you move the price column to the mapping table such that the product price is defined by the combination of product id and size id. And it's fine to have a product with a single size - it just happens to be a one-to-one relationship inside the mapping table.|||

Quote:

Originally Posted by davef

I'll try that thanks

database Design Idea

Good morning
In messages system i have table to store messages and another table to contain the links which the message is posted through
Now i want to delete a link but i don't want to delete the message sent through this link
The problem:
When displaying the message sent what will be found in the link field (i.e. for the deleted link)

If any one has a good idea for doing that please reply to me
I dont want the sql code
I want just the idea
Thanks

kind regards
mohammed Al MaghrabyIs this a one-to-one relationship? one message for one link? Or, could one link refer to many messages? Or vice-versa.|||it is one to many relationship
one link can be used for many massages|||one link can be used for many messages

therefore the message has a foreign key to the link

now you delete the link, and you want the message to remain

and you're asking what will be found in the link

the answer: whatever you defined in the foreign key's ON DELETE option

in sql server 2000, the ON DELETE options are CASCADE (which you don't want) and NO ACTION (which would prevent the delete), so in sql server 2000, you can't do what you want with a foreign key!!

in sql server 2005, the ON DELETE options are NO ACTION, CASCADE, SET NULL, and SET DEFAULT (more in line with the various options defined by the sql standard)

helps?|||First Thanks for interest

There is misunderstanding in the problem
I have to enable the user to add a link with the same name because the original link supposed to be deleted
I.e. it doesn't appear in the forms of the application
So the user thinks that the link was deleted
Again

i want to delete the link because it became useless for me but at the same time there are messages in the message table which are related to that link
And I don't want to delete them
Also at the same time after deleting the link the user wants to view a report about the messages which contain information about the link used for sending these messages
, after deleting the link where could I get the link information?...because the message link data is very importatnt to me to know ......... that is the problem

I hope the situation is clear

Again thanks for interest

Kind regards

Mohammed Al Maghraby|||It sounds like you might want a sort of archive or history table. Just have a "deleted_links" table. The messages can remain in the messages table, they will just relate to the link you move to the deleted_links table. Or you could add a "current" field to the links table. Instead of deleting the link, you would set current to false.
Is that what you mean?|||I have to enable the user to add a link with the same name because the original link supposed to be deleted

Is "LinkName" (or equivalent) your primary key?

Database Design Help !

Hi !!
We are designing a system where we ask people for their interests and store in into the database and send customize email. Following are the questions:
1) Should we use Identity column as Primary Key and CustomerID column? OR we should create Custom CustomerID and use it as Primary Key? (I have read few articles about Identity column as Primary or not Primary, but need little advice what to accept)
2) We have a Tables called : Interest & Customer_Interest
Customer Table:
CustomerID, Customer Name, Address, Email, Signup Date
Interest Table:

InterestID, InterestName
Customer_Interest: (Need suggestion for How to design this)
Should Table be design like:
Option1: CustomerID, InterestID
Option2: CustomerID, Interest1, Interest2, Interest3, Interest4
i.e.
Lets Say:

Customer table has CustomerA, CustomerB, CustomerC
Interest table has Interest I1, I2, I3, I4
Lets Say CustomerA Signedup for Interest I1, I2, I3 and CustomerB signed up I1, I4
As per Option1:
Customer_Interest Table witll have
CustomerA, I1
CustomerA, I2
CustomerA, I3
CustomerB, I1
CustomerB, I4
OR
As per Option2
Customer_Interest (Where Interest Column is bit column.... 1 = Signed up, 0 = Not Signed up
CustomerA, 1, 1, 1, 0
CustomerB, 1, 0, 0, 1
Which way we should design?
3) If we select Option2, and if we are displaying data in ASP.NET Page, will there be any issue if we use 3 tier architecture?
Thanks !!!You should go with Option 1. It would scale as the interest table changes. Option 2 would be a maintenance nightmare to change as the Interest table changes - not to mention, you would have to interpret the boolean values.|||1) What are the business requirements for identifying a customer? Does the business provide a Customer Number? If the requirements provide a unique identifier, then use what the business provides.
2) As the other poster said, your second option wouldbe a total nightmare. If you really wanted data presented in that view, then you can easily create a view to do that from your normalized table.|||

1) We are going to assign CustomerID. Usually we use Identity Column as CustomerID. After reading these articles, SQL server Forum, this forum etc etc, we started thinking if we should use Identity column as CustomerID or in our system people has to login.. so Can we use Email as Primary Key and use Identity Column as Auto number column or Not to use at all.

2)
I got idea for option 1 and option 2 from reading an article. Interest are going to Change. System will have set of predefined Interest.

Lets say if I go with Option 1:
As a user I selected Interest I1, I2
After sometime (few days) I update my profile. I unregister for Interest I1 but signup for Interest I3.. so now I have Interest I2, I3
In this case, should I remove the row from database for I1, and then add new row to database I3?

|||

1) I've found that IDENTITY are the easiest to use for situations like this. I'd recommend starting them at 10000 to get a consistant length.

2) I'm curious -- I'd be interested in seeing the article you found a suggestion for Option2 in.

The idea is that you have a UserInterest table that contains each users interest -- so every time your interests change, you insert/delete from that table.

If you want a flat view of users with specific interests, just a matter of one join per interest ...
SELECT Username,
CASE WHEN I1.InterestId IS NULL THEN 0 ELSE 1 as Interest1,
CASE WHEN I2.InterestId IS NULL THEN 0 ELSE 1 as Interest2,
CASE WHEN Ix.InterestId IS NULL THEN 0 ELSE 1 as InterestX,
FROM Users
JOIN UserInterests I1 ON Users.UserId = I1.UserId
JOIN UserInterests I2 ON Users.UserId = I2.UserId
JOIN UserInterests IX ON Users.UserId = IX.UserId

|||Alex
1) Here is the article which gave indicated Option 2:
http://www.devx.com/dotnet/Article/20040/0/page/1
2) I liked your suggestion about Starting Identity with 10000 to get consistant length
3) If we use Web Services for Data Access and Business Layer, is it good / bad?
4) How can we apply some software design patterns ? I am looking into MVC but reading few things on web tells me that with .NET 2.0 it has got some issues. Most of our recent development is in .NET 2.0 ? Any ideas?|||

Another Database Design Issue:

We are designing a Shopping Cart forConfectionery Items. For Some Items Customer can select toppings and each toppings cost 0.50$.
Here is Our Sample Table
Products ( 0 = No and 1 = Yes )
ProductID ProductName ProductPrice CanShip CanDeliver
P1 Product 1 25 0 1
P2 product 2 50 1 1
How do we make handle Toppings ?
Lets Say there are 5 toppings options available. For Product 2, customer can choose toppings. How do we handle this in database design?

|||

You'll need a Toppings table, and a table to represnt the one-to-many relationship that you are looking to model.

You will also, of course, need an Orders table. And I would be careful about the way you have the ProductPrice listerd; you might have issues if you decide to change the price...

|||

I have read articles
1)http://www.sqlteam.com/item.asp?ItemID=2599,
2)http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=3804&FORUM_ID=5&CAT_ID=3&Topic_Title=Creating+a+table+of+information+based+from+other+t&Forum_Title=Developer
As per them using Identity Column as Primary Key is not good idea.
Now in our design we have used Identity Column for every table as Primary Key.
Let me write down table
CREATE TABLE [dbo].[AddressBook] (
[AddressBookID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DeliveryZip] (
[ZipCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeliveryRate] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Log] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[EventID] [int] NULL ,
[Category] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Priority] [int] NOT NULL ,
[Severity] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[MachineName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AppDomainName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessID] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ThreadName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32ThreadId] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FormattedMessage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentID] [int] NOT NULL ,
[ShipDate] [datetime] NOT NULL ,
[ShipMethod] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ShipRate] [money] NOT NULL ,
[TaxAmount] [money] NOT NULL ,
[OrderTotal] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Payments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreditCardNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpMonth] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpYear] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressBookID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Prod_Toppings] (
[ProductID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NULL ,
[ModelNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModelName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProductImage] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [varchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CanDeliver] [bit] NOT NULL ,
[CanPickUP] [bit] NOT NULL ,
[CanShip] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart_Toppings] (
[RecordID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Toppings] (
[ToppingID] [int] NOT NULL ,
[ToppingName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

|||

Arbitrarly putting an IDENTITY column on every table is what many of us refer to as an "ID-iot" design. Generally, IDENTITY makes a poor choice as a primary key because it is not predicible or verifiable. But that's a whole other discussion and is something that is very likely out of the scope of your project.
I will comment on your schema though ...
Your naming convention is poor. Data elements should follow ISO-11179 standards. Some examples:
- CanShip should be Shippable_Indicator
- DateCreated should be Creation_Date
- ShoppingCart should be ShoppingCarts
- DeliveryZip should be DeliveryRates
I see no constraints what so ever defined. do you want people to order -6 of an item?
You should avoid the MONEY type. Use DECIMAL instead.
Your model could use some improvement, especially with keys. Eg,
- RecordID is pointless
- An address should be (Cust_Id + Addres_Type)
- OrderTotal should not be in the Orders table. That can be calculated in a view by SUMing the Order_Details

|||

Alex... constraints are defined. However when I generated SQL script I forgot to check that in SQL Server. I will post script again tomorrow once I am at office.

1) Looking at above schema, can you suggest where can we remove Identity Column as Primary Key

2) I agree, we never followed any naming convention standard. Thanks for pointing out. Where can I see ISO-11179 standards? We will work on it.

3) Thanks for pointing Money / Decimal suggestion. Can you tell me why Money should be avoided?
4)
- I got your suggestion for RecordID.
- An address should be (Cust_Id + Addres_Type) -- What does it mean ?
- OrderTotal should not be in the Orders table. That can be calculated in a view by SUMing the Order_Details. -- How do I calculate OrderTotal along with Shipping and Tax from Order_Details

|||Don't take that first SQL Team article too seriously; the gut really has little idea of what he is talking about. (If you don't believe me, look at the comments on the article, where there are actaully CALLS FOR THE ARTICLE TO BE RETRACTED. Don't see that very often.)
There is not anything intrisically wrong with using an identity or guid column, and in fact doing so can provide many benefits. If I were you, I'd research the topic more before taking a scapel to all of those identity keys.|||

Thanks pjmcb for looking into it. Now if I see comments from you and Alex, its contradictory. I will look into suggestions Alex made to me.
In the mean while here is the Schema with Constraints as I promised...
CREATE TABLE [dbo].[AddressBook] (
[AddressBookID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[MI] [varchar] (2) NULL ,
[LastName] [varchar] (50) NOT NULL ,
[Address1] [varchar] (100) NOT NULL ,
[Address2] [varchar] (100) NOT NULL ,
[City] [varchar] (50) NOT NULL ,
[State] [varchar] (2) NOT NULL ,
[Zip] [varchar] (10) NOT NULL ,
[Phone] [varchar] (50) NOT NULL ,
[AddressType] [varchar] (20) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Categories] (
[CategoryName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (50) NOT NULL ,
[Password] [varchar] (50) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DeliveryZip] (
[ZipCode] [varchar] (10) NOT NULL ,
[Location] [varchar] (20) NOT NULL ,
[DeliveryRate] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Log] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[EventID] [int] NULL ,
[Category] [nvarchar] (64) NOT NULL ,
[Priority] [int] NOT NULL ,
[Severity] [nvarchar] (32) NOT NULL ,
[Title] [nvarchar] (256) NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[MachineName] [nvarchar] (32) NOT NULL ,
[AppDomainName] [nvarchar] (2048) NOT NULL ,
[ProcessID] [nvarchar] (256) NOT NULL ,
[ProcessName] [nvarchar] (2048) NOT NULL ,
[ThreadName] [nvarchar] (2048) NULL ,
[Win32ThreadId] [nvarchar] (128) NULL ,
[Message] [nvarchar] (2048) NULL ,
[FormattedMessage] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (10000, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentID] [int] NOT NULL ,
[ShipDate] [datetime] NOT NULL ,
[ShipMethod] [varchar] (20) NOT NULL ,
[ShipRate] [money] NOT NULL ,
[TaxAmount] [money] NOT NULL ,
[OrderTotal] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Payments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [varchar] (50) NOT NULL ,
[CreditCardNo] [varchar] (50) NOT NULL ,
[ExpMonth] [varchar] (50) NOT NULL ,
[ExpYear] [varchar] (50) NOT NULL ,
[AddressBookID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Prod_Toppings] (
[ProductID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1000, 1) NOT NULL ,
[SubCategoryID] [int] NOT NULL ,
[ModelNumber] [varchar] (50) NULL ,
[ModelName] [varchar] (50) NOT NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [varchar] (4000) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50) NULL ,
[Quantity] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart_Toppings] (
[RecordID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SubCategories] (
[SubCategoryID] [int] IDENTITY (100, 1) NOT NULL ,
[SubCategoryName] [varchar] (50) NOT NULL ,
[CategoryName] [varchar] (50) NOT NULL ,
[CanShip] [bit] NULL ,
[CanDeliver] [bit] NULL ,
[CanPickup] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Toppings] (
[ToppingName] [varchar] (50) NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AddressBook] ADD
CONSTRAINT [DF_AddressBook_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
CONSTRAINT [PK_AddressBook] PRIMARY KEY CLUSTERED
(
[AddressBookID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Categories] ADD
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryName]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customers] ADD
CONSTRAINT [DF_Customers_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
CONSTRAINT [PK_Customers] PRIMARY KEY NONCLUSTERED
(
[CustomerID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Customers] UNIQUE NONCLUSTERED
(
[EmailAddress]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Log] ADD
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[LogID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderDetails] ADD
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[ItemID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [DF_Orders_OrderDate] DEFAULT (getdate()) FOR [OrderDate],
CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED
(
[OrderID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Orders] UNIQUE NONCLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Payments] ADD
CONSTRAINT [PK_BillingInfo] PRIMARY KEY CLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [DF_Products_ModelNumber] DEFAULT ('') FOR [ModelNumber],
CONSTRAINT [DF_Products_Description] DEFAULT ('') FOR [Description],
CONSTRAINT [PK_Products] PRIMARY KEY NONCLUSTERED
(
[ProductID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ShoppingCart] ADD
CONSTRAINT [DF_ShoppingCart_Quantity] DEFAULT (1) FOR [Quantity],
CONSTRAINT [DF_ShoppingCart_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
CONSTRAINT [PK_ShoppingCart] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SubCategories] ADD
CONSTRAINT [PK_SubCategories] PRIMARY KEY CLUSTERED
(
[SubCategoryID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Toppings] ADD
CONSTRAINT [PK_Toppings] PRIMARY KEY CLUSTERED
(
[ToppingName]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AddressBook] ADD
CONSTRAINT [FK_AddressBook_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
)
GO

ALTER TABLE [dbo].[OrderDetails] ADD
CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[Orders] (
[OrderID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_OrderDetails_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
)
GO

ALTER TABLE [dbo].[Order_Toppings] ADD
CONSTRAINT [FK_Order_Toppings_OrderDetails] FOREIGN KEY
(
[ItemID]
) REFERENCES [dbo].[OrderDetails] (
[ItemID]
),
CONSTRAINT [FK_Order_Toppings_Toppings] FOREIGN KEY
(
[ToppingName]
) REFERENCES [dbo].[Toppings] (
[ToppingName]
)
GO

ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Payments] FOREIGN KEY
(
[PaymentID]
) REFERENCES [dbo].[Payments] (
[PaymentID]
)
GO

ALTER TABLE [dbo].[Payments] ADD
CONSTRAINT [FK_Payments_AddressBook] FOREIGN KEY
(
[AddressBookID]
) REFERENCES [dbo].[AddressBook] (
[AddressBookID]
)
GO

ALTER TABLE [dbo].[Prod_Toppings] ADD
CONSTRAINT [FK_Prod_Toppings_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
),
CONSTRAINT [FK_Prod_Toppings_Toppings] FOREIGN KEY
(
[ToppingName]
) REFERENCES [dbo].[Toppings] (
[ToppingName]
)
GO

ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [FK_Products_SubCategories] FOREIGN KEY
(
[SubCategoryID]
) REFERENCES [dbo].[SubCategories] (
[SubCategoryID]
)
GO

ALTER TABLE [dbo].[ShoppingCart] ADD
CONSTRAINT [FK_ShoppingCart_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
)
GO

ALTER TABLE [dbo].[ShoppingCart_Toppings] ADD
CONSTRAINT [FK_ShoppingCart_Toppings_ShoppingCart] FOREIGN KEY
(
[RecordID]
) REFERENCES [dbo].[ShoppingCart] (
[RecordID]
)
GO

ALTER TABLE [dbo].[SubCategories] ADD
CONSTRAINT [FK_SubCategories_Categories] FOREIGN KEY
(
[CategoryName]
) REFERENCES [dbo].[Categories] (
[CategoryName]
)
GO

|||

Good to hear you have constriants -- a lot of folks don't use them.
ISO-11179 has now officially been opened up for free. The wikipedia has a very basic article on it, so go search in there for it. I hear it has links to download the standards. The most useful part you will find is part-5. That contains lots of info on how to name your data elements.

The money type is completely worthless and an overall pain to work with. It's propietary and you always need to include a currency symbol infront of it. Makes selecting and querying for it a complete pain in the butt. If your products will be in different currencies, you should split that into two columns (Cost_Amt, Cost_Cur) and use ISO 4217 codes for currency. If you ever need to convert currencies, you can have a simple EXCHANGE_RATES table and do a quick join. Try doing that with MONEY type.

I've looked at your tables, here's where you can improve your keys and other things ...

[AddressBook] - I would name this Addresses or CustomerAddresses. Consider that a customer can have only one address of each type (which I'm assuming is user-defined, like "Home" or "Work"). Therefore, the PK should be (CustId, AddressType).

[Categories] - Who defines these and how will they be used? Generally I would say have CategoryName be the PK, but if it's part of a URL, that can get ugly. I'm assuming that they will be part of the URL, so I would recommend making a Category_Code, 3 or 5 digits (don't know how many or what categories there are), and codifying the categories.

[Customers] - This looks fine although who wants to be Customer #12? seed your identity at 10000, 100000 or whatever you think is one greater than the magnitude of customers (1000's of customers, use 10000, etc).

[DeliveryZip] - I would name this DeliveryRates. ZIP Codes are 5 digits, and only 5 digits (CHAR(5)). ZIP+4 consist of two parts: 5 and 4 digits. Pick which one you want to use, and use it consistantly. Also, I have no idea what Location is. Is this a Longitude/Lattitude? A city name? If you want to use this is a city lookup, that's fine, but include a state. Then you can auto-populate the City/State when customer enters ZIP code.

[Log] - I have no idea how your using log table, generally these are used to just dump logging/tracint data into. I would recommend having your log table be *off* primary table space and contain no primary key. It's just a conviennt way to have a flat file.

[Orders] - I would strongly recommend coming up with a keying scheme for your orders. This will make things monumentally easier for users of the system. It depends on how many orders you will be getting, and that sort of thing, but here is one I use
Y-DDD-SSSS (Y is last digit of year, DDD is day of year, SSSS is a sequence number for the number of orders in that day. should be estimated magnitude+1).

I would store Tax info at the item level -- not all items are taxable, and some items have different tax rates. You can make your view containing the totals really easily:
SELECT O.[OrderId], ..., I.Item_Amount, I.Tax_Total,
I.Item_Amount + I.Tax_Total + O.Shipping_Amount AS Grand_Total
FROM [Orders] O INNER JOIN
(SELECT [OrderId]
SUM([Item_Amount]) AS [Sub_Total],
SUM([Tax_Amount]) AS [Tax_Total]
FROM [Order_Items]
GROUP BY [OrderId]) OI ON O.OrderId = I.OrderId

[Payments] - Wrong name; should be StoredCreditCards, or something to that effect. Payments implies that it contains payments. The primary key should be (CustId, StoredCreditCards_Seq), Seq being a sequential number that starts at one for each customer. Or you could have the customers name their cards. Whjatever.

[Products] - I would also recommend coming up with something other than ProductId as your PK. Is there no SKU to use?

[ShoppingCart] - RecordID is completely unecessary. The PK should be CartID. You may want to consider making a random number/code up for Cart identifiers (I use LEFT(CAST(NEWID() AS VARCHAR(32)),8) By making them sequential and predicable, people can edit thgeir cookies and take control of other carts.

[ShoppingCart_Toppings] - Again record id is unecessary. PK Should be (CartId, ProductId).

|||Alex,
Thanks for your suggestions.
We are looking into it. I don't know if you looked into the revised schema I posted or not.
[AddressBook]:
- We will rename it
- We can't use CustID + AddressType as PK. Since this is a shopping, person can have more than one Shipping or Billing Address. (Something similar to Amazon or BN who allows you to store multiple shipping and billing address)
[Categories]:
- Client Defined. We removed Identity Column and used Category Name as PK
[Customers]:
- Made change as per your suggestion
[DeliveryZip]
- Looking into it
[Log]:
- Error Logs.
[Orders]:
- Will have to think if we can do that or go with Identity starting with 100000 or something.
[Payments]:
- I think we don't need to rename it. Payment actually stores Credit Card info and amount charged on their Credit card. We never show customer their Credit Card info upon their next visit. Payment ID is referenced in Orders Table to know how customer has paid and how much was charged to him including everything.
Correct me if I am wrong.
[Products]:
- Client doesn't have SKU for products. We may go with Identity column starting with 1000 or something.
[ShoppingCart]:
- CartID is not sequential. It is random.
- CartID can't be PK coz, if I add more than 1 item into my cart, CartID is going to get repeated.
E.g.
CartID ProductID Qty
AAAA 1000 2
AAAA 2012 1
-- We have used IBuySpy Portal as our reference and some of the design is based on that. This indicates to us that we can't depend on design like that. Correct?

Database design for status field

Hello,

I have a database design question. I want to store a status in a table. In the ASP.NET interface for the user this status will be adapted to the language of the user.

This is how it would look like:

StatusID 1 = "yes" for English, "oui" for French, "ja" for Dutch
StatusID 2 = "no" for English, "non" for French, "neen" for Dutch
StatusID 3 = "error" for English, "erreur" for French, "fout" for Dutch

I don't want to do the translation in ASP.NET and it should be done in one query.

How would it look like in the database design.

WimVM, how about this,

if you are asking about how to design a table or tables, you could have one table (status) like this:

statusID is you primary key and three additional fields (english, french, dutch)

so for instance if you have an english speaker your "yes" would be returned by

declare @.statusintset status = (getYourIdFromApp)select english from status where statusId = @.status

hope this is helpful -- jp|||

Hello,

Thanks for your reply. This would indeed do it, nice and easy. What I forgot to say was that the language is set by an ID, LanguageID. This holds the value 1 for English, 2 for Dutch, 3 for French and 4 for German.

I would need a way that I have more flexibility when I add or disable a language. In the above example you need to know the position of the value linked to the value. Sorry for not informing you about this in the first place.

Thanks

|||

WimVM:
Hello,
Thanks for your reply. This would indeed do it, nice and easy. What I forgot to say was that the language is set by an ID, LanguageID. This holds the value 1 for English, 2 for Dutch, 3 for French and 4 for German.
I would need a way that I have more flexibility when I add or disable a language. In the above example you need to know the position of the value linked to the value. Sorry for not informing you about this in the first place.
Thanks

I still have not found a solution for this. It would be great if somebody could help me with this. Thanks.

|||

WimVM:
Thanks for your reply. This would indeed do it, nice and easy. What I forgot to say was that the language is set by an ID, LanguageID. This holds the value 1 for English, 2 for Dutch, 3 for French and 4 for German.
I would need a way that I have more flexibility when I add or disable a language. In the above example you need to know the position of the value linked to the value. Sorry for not informing you about this in the first place.

Hi Wim, I have some suggestions.

Add a new table to your database called Languages. This table will have 2 fields: LanguageID and LanguageName. The data would look like this:

LanguageID LanguageName
1 English
2 Dutch
3 French
4 German

Add another table called Statuses. This table will have 2 fields: StatusID and StatusName. This table would not necessarily be used in queries, but could be used to store metadata about the Statuses. The data would look like this:

StatusID StatusName
1 Yes Status
2 No Status

Add another table called StatusTranslation. This table will have 3 fields: StatusID, LanguageID, and Translation. The data would look like this:

StatusID LanguageID StatusTranslation
1 1 yes
2 1 no
1 2 ja
2 2 neen
1 3 oui
2 3 non
1 4 ja
2 4 nein

Now, assuming your main data table is called MyTable. This table might look something like this:

ID Column1 Column2 UserLanguageID StatusID
1 Value1 Value2 2 1
2 SomeValue SomeValue2 1 2
3 Value1 Value2 3 1

The query to pull out the status data, translated for the user's language, would look like this:

SELECT
ID,
StatusTranslation
FROM
myTable
INNER JOIN
StatusTranslation ON myTable.StatusID = StatusTranslation.StatusID AND myTable.UserLanguageID = StatusTranslation.UserLanguageID

And the result of that query would be:

ID StatusTranslation
1 ja
2 no
3 oui

This has made some assumptions, such as that you have the UserLanguageID in the same table as the StatusID. You'd need to adjust the queries and data tables to fit your situation, but perhaps this will help get you started.

For some background, you might like Alister Jones' (SomeNewKid) blog post:Localizing the Content, which lead's to Karl Seguin's article:Creating multilingual websites - Part 2.

|||Thankstmorton!

Database Design for Catalog Management

Hi,

I need advice on my design.

I'm developing a system that track the catalog distribution.

I have a 5 table.

Company Table - Store list of company. Each company can have many staff.

Staff Table - Store list of staff.

Catalogs Table - Store list of catalog. Each catalog can have many revision.

PKEY id

name

latest

CatalogsRev Table - Store list of catalog revision.

PKEY catalogID -> Catalogs.id

PKEY year

Staff_Catalog Table - Each staff can have many catalog revision.

PKEY staffID -> Staff.id

PKEY catalogID -> CatalogsRev.catalogID

PKEY year -> CatalogsRev.year

Is the above table good enough to track which company do not have latest catalog revision?

For the Catalogs Table should I use a trigger to update the Catalogs.latest column everytime a new CatalogsRev is entered?

I've read some article on the web says trigger is slow, but in my situation is it suitable to use trigger?

Thanks,

Max

Hi
if u feel u r database satisifes 3rd normal form abs u r database is ok.SO u have to decide u r tables are going to satisify the normalization or not. Ok

U can write a trigger to modify the catalog information. Here u r modifying a column value i think it will not affect on performanace. I think so.

Thank u
Baba

Tuesday, February 14, 2012

Database corrupted since windows reboot

Hi,
My librarian has a problem with is computer. He uses it to store all of
his client's asks, his inventory, etc... on a SQL server's database,
with the help of a third part program, and he never restart his computer.
He few weeks ago, some problems on our city (in France), make his
computer turn off, then restart. And while windows was restarting, it
install to him the latest version of some software - but he didn't
notice which one. And when he turns on the program, the sql server's
database where corrupted, and he wasn't able to use it again:
unfortunately, the program was using it when the computer turn off.
Nonetheless, he didn't made any daily save, so he lost all the
information about his business since a month...
He would like to know if there a way to repair the database, in order to
use it again, instead of restart from 0. He write to the program's
maker, but he wasn't able to restore the data, and a google search
didn't help: none of the answer where about this problem... So is there
any special keywords that I could use, or a known way to repair the base?
Thanks a lot for answer,
Aramiil
Hi,
Could you please answer to below queries:-
1. Are you able to start your SQL Server Service?
2. Did you gone thru the SQL Server Error logs and Event viewer?
3. What is the exact error you ere getting or you have pointed out?
4. How did you come to the conclusion that your database is crached?
Thanks
Hari
SQL Server MVP
"Yann PIQUET" <aramiil@.neobelari.com> wrote in message
news:42dac42e$0$12992$7a628cd7@.news.club-internet.fr...
> Hi,
> My librarian has a problem with is computer. He uses it to store all of
> his client's asks, his inventory, etc... on a SQL server's database, with
> the help of a third part program, and he never restart his computer.
> He few weeks ago, some problems on our city (in France), make his computer
> turn off, then restart. And while windows was restarting, it install to
> him the latest version of some software - but he didn't notice which one.
> And when he turns on the program, the sql server's database where
> corrupted, and he wasn't able to use it again: unfortunately, the program
> was using it when the computer turn off. Nonetheless, he didn't made any
> daily save, so he lost all the information about his business since a
> month...
> He would like to know if there a way to repair the database, in order to
> use it again, instead of restart from 0. He write to the program's maker,
> but he wasn't able to restore the data, and a google search didn't help:
> none of the answer where about this problem... So is there any special
> keywords that I could use, or a known way to repair the base?
> Thanks a lot for answer,
> Aramiil
|||Hi,
Well, I don't really know about SQL server, so I just can answer a few
of your questions:
1=2E Yes, I'm able to start my SQL Server Service, and to use an older
save of the databse, aged of near a month.
2=2E Here are the logs: http://www.neobelari.com/SQLAGENT.zip
3=2E The program (called Octave) display a message with this: "Base
corrompue, impossible d'acceder =E0 la base". I'm not used to work with
SQL server, so I don't have any other informations by the sql server
4=2E idem
Thanks a lot for answer.
Hari Prasad a =E9crit :[vbcol=seagreen]
> Hi,
> Could you please answer to below queries:-
> 1. Are you able to start your SQL Server Service?
> 2. Did you gone thru the SQL Server Error logs and Event viewer?
> 3. What is the exact error you ere getting or you have pointed out?
> 4. How did you come to the conclusion that your database is crached?
>
> Thanks
> Hari
> SQL Server MVP
>
> "Yann PIQUET" <aramiil@.neobelari.com> wrote in message
> news:42dac42e$0$12992$7a628cd7@.news.club-internet.fr...
th[vbcol=seagreen]
ter[vbcol=seagreen]
e=2E[vbcol=seagreen]
am[vbcol=seagreen]
r,[vbcol=seagreen]
|||Hi,
The error logs attached are in French.. Sorry , I am not able to understand.
Thanks
Hari
<apislibrairie@.wanadoo.fr> wrote in message
news:1121684158.709635.26710@.g14g2000cwa.googlegro ups.com...
Hi,
Well, I don't really know about SQL server, so I just can answer a few
of your questions:
1. Yes, I'm able to start my SQL Server Service, and to use an older
save of the databse, aged of near a month.
2. Here are the logs: http://www.neobelari.com/SQLAGENT.zip
3. The program (called Octave) display a message with this: "Base
corrompue, impossible d'acceder la base". I'm not used to work with
SQL server, so I don't have any other informations by the sql server
4. idem
Thanks a lot for answer.
Hari Prasad a crit :[vbcol=seagreen]
> Hi,
> Could you please answer to below queries:-
> 1. Are you able to start your SQL Server Service?
> 2. Did you gone thru the SQL Server Error logs and Event viewer?
> 3. What is the exact error you ere getting or you have pointed out?
> 4. How did you come to the conclusion that your database is crached?
>
> Thanks
> Hari
> SQL Server MVP
>
> "Yann PIQUET" <aramiil@.neobelari.com> wrote in message
> news:42dac42e$0$12992$7a628cd7@.news.club-internet.fr...
|||Hi,
Sorry, I've forgot to translate them... Here are two of them translated
in english (I hope I didn't do too many errors in translation :-/), I'll
post a link to the others one when I'll have finish them.
http://www.neobelari.com/SQLAGENT_eng_part1.zip
Thanks,
Aramiil
Hari Prasad a crit :
> Hi,
> The error logs attached are in French.. Sorry , I am not able to understand.
> Thanks
> Hari
> <apislibrairie@.wanadoo.fr> wrote in message
> news:1121684158.709635.26710@.g14g2000cwa.googlegro ups.com...
> Hi,
> Well, I don't really know about SQL server, so I just can answer a few
> of your questions:
> 1. Yes, I'm able to start my SQL Server Service, and to use an older
> save of the databse, aged of near a month.
> 2. Here are the logs: http://www.neobelari.com/SQLAGENT.zip
> 3. The program (called Octave) display a message with this: "Base
> corrompue, impossible d'acceder la base". I'm not used to work with
> SQL server, so I don't have any other informations by the sql server
> 4. idem
> Thanks a lot for answer.
> Hari Prasad a crit :
>
>

Thursday, March 29, 2012

Database images not showing

Database Idea

Sunday, March 25, 2012

Database for storing Polish characters

Database for storing Polish characters

Database for storing Polish characters

Wednesday, March 7, 2012

database design:dnamically build where clause

Saturday, February 25, 2012

Database design question

Database Design Question

Friday, February 24, 2012

Database design problem

database Design Idea

Database Design Help !

Database design for status field

Database Design for Catalog Management

Tuesday, February 14, 2012

Database corrupted since windows reboot

Database level lock

Blog Archive

About Me