sql server codes: design

Showing posts with label design. Show all posts

Friday, March 30, 2012

Help on understanding generated MDX WHERE clause

When your reporting services datasource is a cube, MDX is generated when you use the design view. In the MDX editor the generated MDX can be viewed. Using parameters I always get a where clause with code like the following:

IIF( STRTOSET(@.OrgLevelname, CONSTRAINED).Count = 1, STRTOSET(@.OrgLevelname, CONSTRAINED), [Organisation].[Level 2 name].currentmember )

I like to understand what is generated. Is there something I can read on the generated WHERE clause (I do understand the generated SELECT and FROM clauses)? Or can someone shed a light on it?

Why does the MDX need to branch on 'Count = 1' In what way does the result slice my data when Count = 1 or when Count <> 1?

Thanks,
Henk

I think this is what Reed Jacobson is talking about here:

http://sqljunkies.com/WebLog/hitachiconsulting/archive/2006/11/06/25176.aspx

To find out more about how subcubes and the Where clause work, see:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/23/subselects_sp2.aspx

HTH,

Chris

sql

help on table design

Hi,
I need a table for the transactions, the transcation could
be credit card, check. they will have different data like
credit card has credit card number, and check have account
number, should I have separate table for each type? or put
them in one table? ThanksThis is a multi-part message in MIME format.
--=_NextPart_000_0183_01C3811B.1B861CE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
As always, it depends. However, checking accounts and credit card =accounts are very different animals. A checking account could pay =interest but a credit card would not. A credit card has an expiry date. = A checking account does not. I'd be inclined to model these =separately. You can always use a UNION ALL to display the account =numbers in a single resultset.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jen" <follower1999@.yahoo.com> wrote in message =news:060401c3813b$59a8e3c0$a301280a@.phx.gbl...
Hi,
I need a table for the transactions, the transcation could be credit card, check. they will have different data like credit card has credit card number, and check have account number, should I have separate table for each type? or put them in one table? Thanks
--=_NextPart_000_0183_01C3811B.1B861CE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

As always, it depends. However, =checking accounts and credit card accounts are very different animals. A =checking account could pay interest but a credit card would not. A credit =card has an expiry date. A checking account does not. I'd be inclined =to model these separately. You can always use a UNION ALL to display =the account numbers in a single resultset.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jen" =wrote in message news:060401c3813b$59=a8e3c0$a301280a@.phx.gbl...Hi,I need a table for the transactions, the transcation could be credit =card, check. they will have different data like credit card has credit =card number, and check have account number, should I have separate table =for each type? or put them in one table? Thanks

--=_NextPart_000_0183_01C3811B.1B861CE0--

Friday, March 23, 2012

Help on design table!

Hi,
I'm using SQL 2000. I have table called invoice which has 2 cols "invoice"
(var 10) / "invoicedate" (var 25).
The question will rise up that why I defined col "invoicedate" as var.
That's my mistake when I was new to sql table design. At the front end of
the application, user scans the invoices into this table without seeing
'invoicedate' field. However I like to keep date info in this column, so I
set default value in the table getdate(). By mistake, I defined the type as
var instead of date. Now I'm getting into problem. There are tons of records
in this table. I like to query the table by date which it's so hard.
I like to know at this point, can I change the type to date? Would it cause
to lose the data? Or if you have better idea that would be great help.
I hope I explained the problem properly, if you have any questions, please
feel free let me know.
Thanks,
SarahYou can change the datatype and you should not lose any data values.
However, if any of the rows contain values that would not convert to a
legitimate date, the conversion will fail. You will then have to find the
non-conforming rows and correct them.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Sarah" <sguo@.coopervision.com> wrote in message
news:eSeat6nqGHA.2232@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using SQL 2000. I have table called invoice which has 2 cols "invoice"
> (var 10) / "invoicedate" (var 25).
> The question will rise up that why I defined col "invoicedate" as var.
> That's my mistake when I was new to sql table design. At the front end of
> the application, user scans the invoices into this table without seeing
> 'invoicedate' field. However I like to keep date info in this column, so I
> set default value in the table getdate(). By mistake, I defined the type
> as var instead of date. Now I'm getting into problem. There are tons of
> records in this table. I like to query the table by date which it's so
> hard.
> I like to know at this point, can I change the type to date? Would it
> cause to lose the data? Or if you have better idea that would be great
> help.
> I hope I explained the problem properly, if you have any questions, please
> feel free let me know.
> Thanks,
> Sarah
>|||Do not forget to backup your data before changing to date datatype!|||Another suggestion would be to create a new column and give it a data
type of DateTime. Then run an update statement to copy the data from
var column to the new column.
IF the copy process finishes without error...delete old column.
Whatever you do, make sure you backup your system to a test environment
and convert it there before converting your production machine. Also be
sure to verify the dates for accuracy.
That's why I like my 2 column method, you'll be able to verify the data
before dropping the original column.
In the future it would be helpful if you posted sample data and gave
some specifics like how many rows this table contains.
Sarah wrote:
> Hi,
> I'm using SQL 2000. I have table called invoice which has 2 cols "invoice"
> (var 10) / "invoicedate" (var 25).
> The question will rise up that why I defined col "invoicedate" as var.
> That's my mistake when I was new to sql table design. At the front end of
> the application, user scans the invoices into this table without seeing
> 'invoicedate' field. However I like to keep date info in this column, so I
> set default value in the table getdate(). By mistake, I defined the type as
> var instead of date. Now I'm getting into problem. There are tons of records
> in this table. I like to query the table by date which it's so hard.
> I like to know at this point, can I change the type to date? Would it cause
> to lose the data? Or if you have better idea that would be great help.
> I hope I explained the problem properly, if you have any questions, please
> feel free let me know.
> Thanks,
> Sarah|||What is the format in the "invoicedate" column? ie mmddyyy or ddmmyyyy
etc.
Sarah wrote:
> Hi,
> I'm using SQL 2000. I have table called invoice which has 2 cols "invoice"
> (var 10) / "invoicedate" (var 25).
> The question will rise up that why I defined col "invoicedate" as var.
> That's my mistake when I was new to sql table design. At the front end of
> the application, user scans the invoices into this table without seeing
> 'invoicedate' field. However I like to keep date info in this column, so I
> set default value in the table getdate(). By mistake, I defined the type as
> var instead of date. Now I'm getting into problem. There are tons of records
> in this table. I like to query the table by date which it's so hard.
> I like to know at this point, can I change the type to date? Would it cause
> to lose the data? Or if you have better idea that would be great help.
> I hope I explained the problem properly, if you have any questions, please
> feel free let me know.
> Thanks,
> Sarah|||Hi All,
Thanks so much for all the responses. I will take Izzy's idea to try. Do it
in test system to see. Also the data which has been in the column is like
"mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into datatime
type in the new column? I like to keep old and new data in the same format
like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
function that would be great.
Thanks again,
Sarah
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
> Another suggestion would be to create a new column and give it a data
> type of DateTime. Then run an update statement to copy the data from
> var column to the new column.
> IF the copy process finishes without error...delete old column.
> Whatever you do, make sure you backup your system to a test environment
> and convert it there before converting your production machine. Also be
> sure to verify the dates for accuracy.
> That's why I like my 2 column method, you'll be able to verify the data
> before dropping the original column.
> In the future it would be helpful if you posted sample data and gave
> some specifics like how many rows this table contains.
>
> Sarah wrote:
>> Hi,
>> I'm using SQL 2000. I have table called invoice which has 2 cols
>> "invoice"
>> (var 10) / "invoicedate" (var 25).
>> The question will rise up that why I defined col "invoicedate" as var.
>> That's my mistake when I was new to sql table design. At the front end of
>> the application, user scans the invoices into this table without seeing
>> 'invoicedate' field. However I like to keep date info in this column, so
>> I
>> set default value in the table getdate(). By mistake, I defined the type
>> as
>> var instead of date. Now I'm getting into problem. There are tons of
>> records
>> in this table. I like to query the table by date which it's so hard.
>> I like to know at this point, can I change the type to date? Would it
>> cause
>> to lose the data? Or if you have better idea that would be great help.
>> I hope I explained the problem properly, if you have any questions,
>> please
>> feel free let me know.
>> Thanks,
>> Sarah
>|||Hi,
So after you create the new column in Invoice table, to convert varchar
to datetime datatype, you can do something like this:
/* Create a table to store Varchar Invoice dates datatype */
Create Table #Temp (
InvoinceDate Varchar(25)
)
/* Insert Varchar Invoice dates into temp table
from Invoice table */
Insert #Temp (
InvoinceDate
)
Select InvoiceDate From Invoice
/* Insert into new InvoiceDate column with DateTime datatype in
Invoice after converting varchar to datetime datatype */
Insert Invoice (
NewInvoiceDate
)
Select Convert(DateTime, InvoiceDate, 1) As NewInvoiceDate
>From #Temp
/* Drop temp table */
Drop Table #Temp
Cheers!
- Lem
Sarah wrote:
> Hi All,
> Thanks so much for all the responses. I will take Izzy's idea to try. Do it
> in test system to see. Also the data which has been in the column is like
> "mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into datatime
> type in the new column? I like to keep old and new data in the same format
> like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
> function that would be great.
> Thanks again,
> Sarah
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
> > Another suggestion would be to create a new column and give it a data
> > type of DateTime. Then run an update statement to copy the data from
> > var column to the new column.
> >
> > IF the copy process finishes without error...delete old column.
> >
> > Whatever you do, make sure you backup your system to a test environment
> > and convert it there before converting your production machine. Also be
> > sure to verify the dates for accuracy.
> >
> > That's why I like my 2 column method, you'll be able to verify the data
> > before dropping the original column.
> >
> > In the future it would be helpful if you posted sample data and gave
> > some specifics like how many rows this table contains.
> >
> >
> > Sarah wrote:
> >> Hi,
> >>
> >> I'm using SQL 2000. I have table called invoice which has 2 cols
> >> "invoice"
> >> (var 10) / "invoicedate" (var 25).
> >> The question will rise up that why I defined col "invoicedate" as var.
> >> That's my mistake when I was new to sql table design. At the front end of
> >> the application, user scans the invoices into this table without seeing
> >> 'invoicedate' field. However I like to keep date info in this column, so
> >> I
> >> set default value in the table getdate(). By mistake, I defined the type
> >> as
> >> var instead of date. Now I'm getting into problem. There are tons of
> >> records
> >> in this table. I like to query the table by date which it's so hard.
> >> I like to know at this point, can I change the type to date? Would it
> >> cause
> >> to lose the data? Or if you have better idea that would be great help.
> >> I hope I explained the problem properly, if you have any questions,
> >> please
> >> feel free let me know.
> >>
> >> Thanks,
> >> Sarah
> >|||Hello Lem,
Thanks for the detail. I will do this to see. Thanks a lot
<lgalumbres@.gmail.com> wrote in message
news:1153253362.404116.316160@.b28g2000cwb.googlegroups.com...
> Hi,
> So after you create the new column in Invoice table, to convert varchar
> to datetime datatype, you can do something like this:
> /* Create a table to store Varchar Invoice dates datatype */
> Create Table #Temp (
> InvoinceDate Varchar(25)
> )
> /* Insert Varchar Invoice dates into temp table
> from Invoice table */
> Insert #Temp (
> InvoinceDate
> )
> Select InvoiceDate From Invoice
> /* Insert into new InvoiceDate column with DateTime datatype in
> Invoice after converting varchar to datetime datatype */
> Insert Invoice (
> NewInvoiceDate
> )
> Select Convert(DateTime, InvoiceDate, 1) As NewInvoiceDate
>>From #Temp
> /* Drop temp table */
> Drop Table #Temp
> Cheers!
> - Lem
> Sarah wrote:
>> Hi All,
>> Thanks so much for all the responses. I will take Izzy's idea to try. Do
>> it
>> in test system to see. Also the data which has been in the column is like
>> "mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into
>> datatime
>> type in the new column? I like to keep old and new data in the same
>> format
>> like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
>> function that would be great.
>> Thanks again,
>> Sarah
>> "Izzy" <israel.richner@.gmail.com> wrote in message
>> news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
>> > Another suggestion would be to create a new column and give it a data
>> > type of DateTime. Then run an update statement to copy the data from
>> > var column to the new column.
>> >
>> > IF the copy process finishes without error...delete old column.
>> >
>> > Whatever you do, make sure you backup your system to a test environment
>> > and convert it there before converting your production machine. Also be
>> > sure to verify the dates for accuracy.
>> >
>> > That's why I like my 2 column method, you'll be able to verify the data
>> > before dropping the original column.
>> >
>> > In the future it would be helpful if you posted sample data and gave
>> > some specifics like how many rows this table contains.
>> >
>> >
>> > Sarah wrote:
>> >> Hi,
>> >>
>> >> I'm using SQL 2000. I have table called invoice which has 2 cols
>> >> "invoice"
>> >> (var 10) / "invoicedate" (var 25).
>> >> The question will rise up that why I defined col "invoicedate" as var.
>> >> That's my mistake when I was new to sql table design. At the front end
>> >> of
>> >> the application, user scans the invoices into this table without
>> >> seeing
>> >> 'invoicedate' field. However I like to keep date info in this column,
>> >> so
>> >> I
>> >> set default value in the table getdate(). By mistake, I defined the
>> >> type
>> >> as
>> >> var instead of date. Now I'm getting into problem. There are tons of
>> >> records
>> >> in this table. I like to query the table by date which it's so hard.
>> >> I like to know at this point, can I change the type to date? Would it
>> >> cause
>> >> to lose the data? Or if you have better idea that would be great help.
>> >> I hope I explained the problem properly, if you have any questions,
>> >> please
>> >> feel free let me know.
>> >>
>> >> Thanks,
>> >> Sarah
>> >
>|||Wouldn't it be a lot easier to do it like this
UPDATE INVOICE
SET NEW_INVOICEDATE = Convert(DateTime, InvoiceDate, 1)
That temp table idea looks a little scary, how will it ensure the right
dates get reinserted into the correct rows.
Long as the update completes with no errors AND the data looks the same
then you could drop the old column.
Sarah wrote:
> Hello Lem,
> Thanks for the detail. I will do this to see. Thanks a lot
> <lgalumbres@.gmail.com> wrote in message
> news:1153253362.404116.316160@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > So after you create the new column in Invoice table, to convert varchar
> > to datetime datatype, you can do something like this:
> >
> > /* Create a table to store Varchar Invoice dates datatype */
> > Create Table #Temp (
> > InvoinceDate Varchar(25)
> > )
> > /* Insert Varchar Invoice dates into temp table
> > from Invoice table */
> > Insert #Temp (
> > InvoinceDate
> > )
> > Select InvoiceDate From Invoice
> > /* Insert into new InvoiceDate column with DateTime datatype in
> > Invoice after converting varchar to datetime datatype */
> > Insert Invoice (
> > NewInvoiceDate
> > )
> > Select Convert(DateTime, InvoiceDate, 1) As NewInvoiceDate
> >>From #Temp
> >
> > /* Drop temp table */
> > Drop Table #Temp
> >
> > Cheers!
> > - Lem
> >
> > Sarah wrote:
> >> Hi All,
> >>
> >> Thanks so much for all the responses. I will take Izzy's idea to try. Do
> >> it
> >> in test system to see. Also the data which has been in the column is like
> >> "mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into
> >> datatime
> >> type in the new column? I like to keep old and new data in the same
> >> format
> >> like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
> >> function that would be great.
> >>
> >> Thanks again,
> >> Sarah
> >> "Izzy" <israel.richner@.gmail.com> wrote in message
> >> news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
> >> > Another suggestion would be to create a new column and give it a data
> >> > type of DateTime. Then run an update statement to copy the data from
> >> > var column to the new column.
> >> >
> >> > IF the copy process finishes without error...delete old column.
> >> >
> >> > Whatever you do, make sure you backup your system to a test environment
> >> > and convert it there before converting your production machine. Also be
> >> > sure to verify the dates for accuracy.
> >> >
> >> > That's why I like my 2 column method, you'll be able to verify the data
> >> > before dropping the original column.
> >> >
> >> > In the future it would be helpful if you posted sample data and gave
> >> > some specifics like how many rows this table contains.
> >> >
> >> >
> >> > Sarah wrote:
> >> >> Hi,
> >> >>
> >> >> I'm using SQL 2000. I have table called invoice which has 2 cols
> >> >> "invoice"
> >> >> (var 10) / "invoicedate" (var 25).
> >> >> The question will rise up that why I defined col "invoicedate" as var.
> >> >> That's my mistake when I was new to sql table design. At the front end
> >> >> of
> >> >> the application, user scans the invoices into this table without
> >> >> seeing
> >> >> 'invoicedate' field. However I like to keep date info in this column,
> >> >> so
> >> >> I
> >> >> set default value in the table getdate(). By mistake, I defined the
> >> >> type
> >> >> as
> >> >> var instead of date. Now I'm getting into problem. There are tons of
> >> >> records
> >> >> in this table. I like to query the table by date which it's so hard.
> >> >> I like to know at this point, can I change the type to date? Would it
> >> >> cause
> >> >> to lose the data? Or if you have better idea that would be great help.
> >> >> I hope I explained the problem properly, if you have any questions,
> >> >> please
> >> >> feel free let me know.
> >> >>
> >> >> Thanks,
> >> >> Sarah
> >> >
> >|||OH...about the date format.
It doesn't matter how it's stored in SQL Server it only matters how you
format and display the date in your client application.
As far as SQL is concerned 2006-07-18 is the same as 7/18/2006.
Izzy wrote:
> Wouldn't it be a lot easier to do it like this
> UPDATE INVOICE
> SET NEW_INVOICEDATE = Convert(DateTime, InvoiceDate, 1)
> That temp table idea looks a little scary, how will it ensure the right
> dates get reinserted into the correct rows.
> Long as the update completes with no errors AND the data looks the same
> then you could drop the old column.
> Sarah wrote:
> > Hello Lem,
> >
> > Thanks for the detail. I will do this to see. Thanks a lot
> > <lgalumbres@.gmail.com> wrote in message
> > news:1153253362.404116.316160@.b28g2000cwb.googlegroups.com...
> > > Hi,
> > >
> > > So after you create the new column in Invoice table, to convert varchar
> > > to datetime datatype, you can do something like this:
> > >
> > > /* Create a table to store Varchar Invoice dates datatype */
> > > Create Table #Temp (
> > > InvoinceDate Varchar(25)
> > > )
> > > /* Insert Varchar Invoice dates into temp table
> > > from Invoice table */
> > > Insert #Temp (
> > > InvoinceDate
> > > )
> > > Select InvoiceDate From Invoice
> > > /* Insert into new InvoiceDate column with DateTime datatype in
> > > Invoice after converting varchar to datetime datatype */
> > > Insert Invoice (
> > > NewInvoiceDate
> > > )
> > > Select Convert(DateTime, InvoiceDate, 1) As NewInvoiceDate
> > >>From #Temp
> > >
> > > /* Drop temp table */
> > > Drop Table #Temp
> > >
> > > Cheers!
> > > - Lem
> > >
> > > Sarah wrote:
> > >> Hi All,
> > >>
> > >> Thanks so much for all the responses. I will take Izzy's idea to try. Do
> > >> it
> > >> in test system to see. Also the data which has been in the column is like
> > >> "mmm dd yyyy hh:mm:ss am or pm". How would I convert old data into
> > >> datatime
> > >> type in the new column? I like to keep old and new data in the same
> > >> format
> > >> like "mmm dd yyyy" or "mm/dd/yy". Is it possible? If you can show me the
> > >> function that would be great.
> > >>
> > >> Thanks again,
> > >> Sarah
> > >> "Izzy" <israel.richner@.gmail.com> wrote in message
> > >> news:1153241956.118537.211970@.h48g2000cwc.googlegroups.com...
> > >> > Another suggestion would be to create a new column and give it a data
> > >> > type of DateTime. Then run an update statement to copy the data from
> > >> > var column to the new column.
> > >> >
> > >> > IF the copy process finishes without error...delete old column.
> > >> >
> > >> > Whatever you do, make sure you backup your system to a test environment
> > >> > and convert it there before converting your production machine. Also be
> > >> > sure to verify the dates for accuracy.
> > >> >
> > >> > That's why I like my 2 column method, you'll be able to verify the data
> > >> > before dropping the original column.
> > >> >
> > >> > In the future it would be helpful if you posted sample data and gave
> > >> > some specifics like how many rows this table contains.
> > >> >
> > >> >
> > >> > Sarah wrote:
> > >> >> Hi,
> > >> >>
> > >> >> I'm using SQL 2000. I have table called invoice which has 2 cols
> > >> >> "invoice"
> > >> >> (var 10) / "invoicedate" (var 25).
> > >> >> The question will rise up that why I defined col "invoicedate" as var.
> > >> >> That's my mistake when I was new to sql table design. At the front end
> > >> >> of
> > >> >> the application, user scans the invoices into this table without
> > >> >> seeing
> > >> >> 'invoicedate' field. However I like to keep date info in this column,
> > >> >> so
> > >> >> I
> > >> >> set default value in the table getdate(). By mistake, I defined the
> > >> >> type
> > >> >> as
> > >> >> var instead of date. Now I'm getting into problem. There are tons of
> > >> >> records
> > >> >> in this table. I like to query the table by date which it's so hard.
> > >> >> I like to know at this point, can I change the type to date? Would it
> > >> >> cause
> > >> >> to lose the data? Or if you have better idea that would be great help.
> > >> >> I hope I explained the problem properly, if you have any questions,
> > >> >> please
> > >> >> feel free let me know.
> > >> >>
> > >> >> Thanks,
> > >> >> Sarah
> > >> >
> > >

Help on design table!

Help on design question

Hi,
I have a fundamental design issue that I would appreciate any assistance on.
I have a DB that is aimed at tracking jobs that come into a department and
the charges associated with these jobs.
Most jobs are handled by that dept, but some need to be outsourced to
external suppliers. All the products and services that are provided by that
dept are stored in a table called tblInternalItems with the primary key bein
g
a field called ItemID. If a job requires the use of an external supplier, I
store that info in a table called tblExternalItems with the primary key bein
g
a field called ItemID. Because this info needs to be accounted for and
accounts notified at the end of each month on what we owe the suppliers, thi
s
seems to make sense and the final figures easy to calculate.
To keep a track of what customers have had, I have a table called tblJobItem
s.
Now, the big problem comes in with the fact that a customer can have an item
that is provided by my dept, or an item provided by an external supplier.
So, I have a field here called ItemID, that being the foreign key between th
e
2 tables. Now that's the big question - I don't think I can enforce good
referential rules in this type of situation where the required value can com
e
from 2 different tables.
There are 2 solutions I can think of. Introduce another field in the
tblJobItems table to track external supplier items, or merge the two tables
into one.
I am leaning towards a single table with just an additional field called
SupplierID to track any jobs that have been outsourced.
That would then make referential integrity easily enforcable.
That's what i suspect but would appreciate input to confirm my thoughts
before I go ahead and change things.
Many thanks in advance for any assistance
A confused MarekI would put all the items in a single table (because what you're really
talking about, from a data design perspective, is a single entity). Then
you'd just have to add another column to that table that indicates the
source of the work (initially internal or external but it could be expanded
to be a supplierID where the internal department is just one of those
suppliers - might come in handy later on down the track and your accounts
dept might decide they'd like cost breakdowns by supplier). It makes the
allocation of unique itemIDs much easier when you don't have to co-ordinate
between 2 different tables, solves your DRI problem and querying the items
also becomes easier IMHO.
That's my 2c worth. HTH.
Cheers,
Mike
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
> on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
> that
> dept are stored in a table called tblInternalItems with the primary key
> being
> a field called ItemID. If a job requires the use of an external supplier,
> I
> store that info in a table called tblExternalItems with the primary key
> being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
> this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
> tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
> item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
> the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
> come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
> tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek|||Thanks for the swift response Mike. Confirms my thoughts too so will swiftl
y
change my design.
Marek
"Mike Hodgson" wrote:

> I would put all the items in a single table (because what you're really
> talking about, from a data design perspective, is a single entity). Then
> you'd just have to add another column to that table that indicates the
> source of the work (initially internal or external but it could be expande
d
> to be a supplierID where the internal department is just one of those
> suppliers - might come in handy later on down the track and your accounts
> dept might decide they'd like cost breakdowns by supplier). It makes the
> allocation of unique itemIDs much easier when you don't have to co-ordinat
e
> between 2 different tables, solves your DRI problem and querying the items
> also becomes easier IMHO.
> That's my 2c worth. HTH.
> --
> Cheers,
> Mike
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
>
>|||Mike suggests a much more scalable design... In your first design if there
were another kind of thing, you'd have to create yet another table for
it...Now you can simply add a new row with new, different type field value..
Good job!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
that
> dept are stored in a table called tblInternalItems with the primary key
being
> a field called ItemID. If a job requires the use of an external supplier,
I
> store that info in a table called tblExternalItems with the primary key
being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Mareksql

Help on design question

Hi,
I have a fundamental design issue that I would appreciate any assistance on.
I have a DB that is aimed at tracking jobs that come into a department and
the charges associated with these jobs.
Most jobs are handled by that dept, but some need to be outsourced to
external suppliers. All the products and services that are provided by that
dept are stored in a table called tblInternalItems with the primary key being
a field called ItemID. If a job requires the use of an external supplier, I
store that info in a table called tblExternalItems with the primary key being
a field called ItemID. Because this info needs to be accounted for and
accounts notified at the end of each month on what we owe the suppliers, this
seems to make sense and the final figures easy to calculate.
To keep a track of what customers have had, I have a table called tblJobItems.
Now, the big problem comes in with the fact that a customer can have an item
that is provided by my dept, or an item provided by an external supplier.
So, I have a field here called ItemID, that being the foreign key between the
2 tables. Now that's the big question - I don't think I can enforce good
referential rules in this type of situation where the required value can come
from 2 different tables.
There are 2 solutions I can think of. Introduce another field in the
tblJobItems table to track external supplier items, or merge the two tables
into one.
I am leaning towards a single table with just an additional field called
SupplierID to track any jobs that have been outsourced.
That would then make referential integrity easily enforcable.
That's what i suspect but would appreciate input to confirm my thoughts
before I go ahead and change things.
--
Many thanks in advance for any assistance
A confused MarekI would put all the items in a single table (because what you're really
talking about, from a data design perspective, is a single entity). Then
you'd just have to add another column to that table that indicates the
source of the work (initially internal or external but it could be expanded
to be a supplierID where the internal department is just one of those
suppliers - might come in handy later on down the track and your accounts
dept might decide they'd like cost breakdowns by supplier). It makes the
allocation of unique itemIDs much easier when you don't have to co-ordinate
between 2 different tables, solves your DRI problem and querying the items
also becomes easier IMHO.
That's my 2c worth. HTH.
--
Cheers,
Mike
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
> on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
> that
> dept are stored in a table called tblInternalItems with the primary key
> being
> a field called ItemID. If a job requires the use of an external supplier,
> I
> store that info in a table called tblExternalItems with the primary key
> being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
> this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
> tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
> item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
> the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
> come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
> tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek|||Thanks for the swift response Mike. Confirms my thoughts too so will swiftly
change my design.
Marek
"Mike Hodgson" wrote:
> I would put all the items in a single table (because what you're really
> talking about, from a data design perspective, is a single entity). Then
> you'd just have to add another column to that table that indicates the
> source of the work (initially internal or external but it could be expanded
> to be a supplierID where the internal department is just one of those
> suppliers - might come in handy later on down the track and your accounts
> dept might decide they'd like cost breakdowns by supplier). It makes the
> allocation of unique itemIDs much easier when you don't have to co-ordinate
> between 2 different tables, solves your DRI problem and querying the items
> also becomes easier IMHO.
> That's my 2c worth. HTH.
> --
> Cheers,
> Mike
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> > Hi,
> >
> > I have a fundamental design issue that I would appreciate any assistance
> > on.
> > I have a DB that is aimed at tracking jobs that come into a department and
> > the charges associated with these jobs.
> >
> > Most jobs are handled by that dept, but some need to be outsourced to
> > external suppliers. All the products and services that are provided by
> > that
> > dept are stored in a table called tblInternalItems with the primary key
> > being
> > a field called ItemID. If a job requires the use of an external supplier,
> > I
> > store that info in a table called tblExternalItems with the primary key
> > being
> > a field called ItemID. Because this info needs to be accounted for and
> > accounts notified at the end of each month on what we owe the suppliers,
> > this
> > seems to make sense and the final figures easy to calculate.
> >
> > To keep a track of what customers have had, I have a table called
> > tblJobItems.
> >
> > Now, the big problem comes in with the fact that a customer can have an
> > item
> > that is provided by my dept, or an item provided by an external supplier.
> > So, I have a field here called ItemID, that being the foreign key between
> > the
> > 2 tables. Now that's the big question - I don't think I can enforce good
> > referential rules in this type of situation where the required value can
> > come
> > from 2 different tables.
> >
> > There are 2 solutions I can think of. Introduce another field in the
> > tblJobItems table to track external supplier items, or merge the two
> > tables
> > into one.
> >
> >
> > I am leaning towards a single table with just an additional field called
> > SupplierID to track any jobs that have been outsourced.
> >
> > That would then make referential integrity easily enforcable.
> >
> > That's what i suspect but would appreciate input to confirm my thoughts
> > before I go ahead and change things.
> >
> >
> >
> > --
> > Many thanks in advance for any assistance
> > A confused Marek
>
>|||Mike suggests a much more scalable design... In your first design if there
were another kind of thing, you'd have to create yet another table for
it...Now you can simply add a new row with new, different type field value..
Good job!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
that
> dept are stored in a table called tblInternalItems with the primary key
being
> a field called ItemID. If a job requires the use of an external supplier,
I
> store that info in a table called tblExternalItems with the primary key
being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek

Help on design question

Hi,
I have a fundamental design issue that I would appreciate any assistance on.
I have a DB that is aimed at tracking jobs that come into a department and
the charges associated with these jobs.
Most jobs are handled by that dept, but some need to be outsourced to
external suppliers. All the products and services that are provided by that
dept are stored in a table called tblInternalItems with the primary key being
a field called ItemID. If a job requires the use of an external supplier, I
store that info in a table called tblExternalItems with the primary key being
a field called ItemID. Because this info needs to be accounted for and
accounts notified at the end of each month on what we owe the suppliers, this
seems to make sense and the final figures easy to calculate.
To keep a track of what customers have had, I have a table called tblJobItems.
Now, the big problem comes in with the fact that a customer can have an item
that is provided by my dept, or an item provided by an external supplier.
So, I have a field here called ItemID, that being the foreign key between the
2 tables. Now that's the big question - I don't think I can enforce good
referential rules in this type of situation where the required value can come
from 2 different tables.
There are 2 solutions I can think of. Introduce another field in the
tblJobItems table to track external supplier items, or merge the two tables
into one.
I am leaning towards a single table with just an additional field called
SupplierID to track any jobs that have been outsourced.
That would then make referential integrity easily enforcable.
That's what i suspect but would appreciate input to confirm my thoughts
before I go ahead and change things.
Many thanks in advance for any assistance
A confused Marek
I would put all the items in a single table (because what you're really
talking about, from a data design perspective, is a single entity). Then
you'd just have to add another column to that table that indicates the
source of the work (initially internal or external but it could be expanded
to be a supplierID where the internal department is just one of those
suppliers - might come in handy later on down the track and your accounts
dept might decide they'd like cost breakdowns by supplier). It makes the
allocation of unique itemIDs much easier when you don't have to co-ordinate
between 2 different tables, solves your DRI problem and querying the items
also becomes easier IMHO.
That's my 2c worth. HTH.
Cheers,
Mike
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
> on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
> that
> dept are stored in a table called tblInternalItems with the primary key
> being
> a field called ItemID. If a job requires the use of an external supplier,
> I
> store that info in a table called tblExternalItems with the primary key
> being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
> this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
> tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
> item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
> the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
> come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
> tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek
|||Thanks for the swift response Mike. Confirms my thoughts too so will swiftly
change my design.
Marek
"Mike Hodgson" wrote:

> I would put all the items in a single table (because what you're really
> talking about, from a data design perspective, is a single entity). Then
> you'd just have to add another column to that table that indicates the
> source of the work (initially internal or external but it could be expanded
> to be a supplierID where the internal department is just one of those
> suppliers - might come in handy later on down the track and your accounts
> dept might decide they'd like cost breakdowns by supplier). It makes the
> allocation of unique itemIDs much easier when you don't have to co-ordinate
> between 2 different tables, solves your DRI problem and querying the items
> also becomes easier IMHO.
> That's my 2c worth. HTH.
> --
> Cheers,
> Mike
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
>
>
|||Mike suggests a much more scalable design... In your first design if there
were another kind of thing, you'd have to create yet another table for
it...Now you can simply add a new row with new, different type field value..
Good job!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
that
> dept are stored in a table called tblInternalItems with the primary key
being
> a field called ItemID. If a job requires the use of an external supplier,
I
> store that info in a table called tblExternalItems with the primary key
being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek

Help on DB design

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

It depends,

You have 3 (or more) options,

Store as Individual Table(rows

Each file will be stored as single row with key field.

The master table can hold the key field & number of files

Store as Delimited Value

If you use SQL server 2000, you are not allowed to store more than 8000 chars, otherwise you have to change the data type to LOB(text). In SQL Server 2005 you can use the Varchar(max).

Manipulations need to be done on the UI.

Single row modification can’t be done on the UI, you have to stick with your UI.

Store as XML value

If you use SQL Server 2005, you can use new data type XML.

You can store at any number of chars

Easy to manipulate on Database itself.

Structured & Well Managed

My Rank is – 1.XML, 2.Table(rows), 3.Delimited value

|||^Thank you Manivannan.D.Sekaran . I will follow Table(rows) as i am using sql server 2000.

Help on DB Design

Raj

Quote:

Originally Posted by bootzwiz

Raj

Generate FileId in the database and GroupId and FilePath

FileId FileGroupID FilePath
--------------------------------

Like this|||

Quote:

Originally Posted by bootzwiz

Raj

i offer to have the following entity/table for your application :

1-an entity named "Session " with these fields (SessionID,SessionName,SessionDate ) with SessionID for primary key.

2-an entity named SessionFiles with these fields(SessionID,FilenameWithPath,uploadStatus) with "SessionID,FilenameWithPath" for primary key.

3-make relation for these two table with SessionID field.

4-each form shows one session in your application for uploading the related SessionFiles.

5-Enjoy it.sql

Help on Database Design

I would like to design some tables for offices. In the scenario, there are so many offices which are categorized by different levels. Eg. National level, State level, District level etc... The design must satisfy the future need of adding another intermediate level (say, Zone level in between National and State), without any redesign of database. So how could I materialze this?In one table you can create a column with CHAR(5) that specifies the category of level that can be used as a master table to specify these categories with a future addition of sections.

Sunday, February 19, 2012

Help me design my DB

Hi all.

I'm going to create a big DB that will hold important info
(the usual stuff - Clients, Products, Orders...)

I wonder where should I use the IDENTITY field,
for example - on Orders I will have Order_ID...
(and where does SQL server the numbers of a deleted records)

My fear is that IDENTITY fields will go wrong somehow so I can
loose connections within the tables
(maybe when restoring my DB to some other locations... with DTS... or other issues)

1. When should I use IDENTITY field ?

2. If I do NOT - how can I lock a record when I add a new oneP.S.

How can I alter a table turning ON or OFF an IDENTITY field ?

for example:
if I had my DB with Client_ID as an I IDENTITY field and for some reason it has
changed to just INT (with no IDENTITY) - how can I tell it to be IDENTITY field again ?|||Identity fields are very useful for creating your primary key into the table. Order_ID is a great example. When a new record is created, it automatically gets the next number. Since SQL server handles this for you, you don't have to keep a table of IDs, and all the locking overhead that goes with assigning your own incrementing value, and there's no way to "accidently" get two records with the same ID. The downside is that though it's fairly easy to turn it off while you add a couple records with specific IDs, you'll have to lock the table while you do so to keep other people's inserts from being added while it's turned off. If you see the need to insert records with specific IDs, or the need to modify IDs happing very often, an identity field may not be the right choice for you.

Here's what I have had a lot of success with...
All data tables have an identity ID field that is the PK into that table
All relationships use that ID field as the foreign key.
All inserts are done through stored procs that return the identity value
All updates and deletes are done through stored procs with the ID passed in to be used in the WHERE part of the update/delete command

As for your question about turning identity on/off, use the command...
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
Though it's the opposite what you might think. Setting IDENTITY_INSERT ON will allow you to insert records and specify thier ID, or update records and modify thier IDs. Setting IDENTITY_INSERT OFF (the default) means that the identity fields will get their values automatically on insert and cannot be modified.

Also note, you can only turn on IDENTITY_INSERT for one table at a time.|||Great help - thanks a lot!

U gave me some real good points
and "All relationships use that ID field as the foreign key." that will solve my problem,

I'll just create a new field in some table and bind data using this field and not by using an IDENTITY field...|||be aware of the issues of using identity fields, for instance:

- limited portability.. identity/autoid fields are not standard sql.

- replication issues.

- data transfer issues (this is a big one if you need to move data).

- uniqueness (across the database and then object model).

- size. if you're using int, you could hit the limit pretty quickly if your tables get big.|||Hi m7,

in-fact - the problem started when I had problems import / export data using DTS,
I updated SP3a and all the fixes needed as Microsoft said but nothing helped,
even reInstalled SQL Server...

it was very strange, when I try to export / import using the first option (copy data) it worked
just fine (including creating the table and data), but when I try to import / export as OBJECT
all strange problems arises... and I need this for the IDENTITY fields...

my SQL Server account is on a remote computer...

Anyway - I searched for a tool to allow me import / export data but I could not find a working one...
Microsoft do haveSqlWebAdmin which is nice, but does not seem to work with UNICODE characters...
(I could not export/import tables that has some NTEXT data in it - files not saved as UTF-8 but ANSI)

so my conclusion from all this is NOT to use IDENTITY fields as my connection between tables
but create a custom unique field in all relevant tables so all tables can be binded with.|||a guid would allow your field to be unique across all tables.

it also takes a lot of headaches away when doing data transfer.

i used to think using guids would be slow.. but tested them and they're not *that* much slower than ints. you do HAVE to remember NOT to create a clustered index on the guid column tho for obvious reasons!!!|||And, of course, your last point is even more impacted by GUIDs than by INTs:

>>- size. if you're using int, you could hit the limit pretty quickly if your tables get big.|||> And, of course, your last point is even more impacted by GUIDs than by INTs

nope. i dont talk of size in bytes of your database.

i talk in the fact that what happens if you choose an int, and then end up with a billion rows?

answer: there aren't enough ints.

the problem goes away with guids.|||:: talk in the fact that what happens if you choose an int, and then end up with a billion rows?
::
::answer: there aren't enough ints.

There are. SQL Int is 4 byte signed :-)

But what do you do once you hit 4 billion and some change?|||id meant to write "...end up with a FEW billion rows"

you knew what i meant tho :)|||Hi again,

U said U did try this,
did U try to put an index on this field ?

does it not slow down things because of it's length ?
(did U use just char ?)|||i set up the fields as guids (uniqueidentifier) and let my object framework create the new guid each time i insert a record.

i found the performance to be within 10% (or less) of an int pk for everything (insert, update, retrieve) even with joins.

indexes is where guids have problems. if you dont have one, your retrieve could perform badly. if you do have one, your inserts will perform badly as it will need to re-index (guids arent incremental.. you could get any value).|||>>within 10%

Seems significant, no? Especially on high-load systems?

I've read prior posts on GUIDs as pks, but I would think this might be enough to scare me away...|||within 10% (so often less) is totally insignificant when you consider the benefits.

heck, you could use the guid to cache the object globally and save the cost of using the guid easily.

and that 10% is the performance difference for just the queries. remember, querying on a primary key (or joining on it) is only a small part of a high-load system... i.e. it wont give your performance a 10% reduction overall.

that 10% can easily be bought back (cheaply) with some extra hardware (memory or whatever).

Friday, March 30, 2012

Help on understanding generated MDX WHERE clause

help on table design

Friday, March 23, 2012

Help on design table!

Help on design table!

Help on design question

Help on design question

Help on design question

Help on DB design

Help on DB Design

Help on Database Design

Sunday, February 19, 2012

Help me design my DB

sql server codes

Blog Archive

About Me