Showing posts with label total. Show all posts
Showing posts with label total. Show all posts

Sunday, March 25, 2012

Best practise regarding Tables?

Hi!

I have 6-7 tables total containing subobjects for different objects like phonenumbers and emails for contacts.
This meaning i have to do some querys on each detailpage. I will use stored proc for fetching subobjects.

My question therefore was: if i could merge subobjects into same tables making me use perhaps 2 querys instead of 4 and thus perhaps doubling the size
of the tables would this have a possibility of giving me any performance difference whatsoever?

As i see pros arefewer querys, and cons are larger tables and i will need another field separating the types of objects in the table.

Anyone have insight to this?

I would be curious to see what you mean by "subobjects" - usually when you ask a question like this it is a good idea to post your table structure. You'll get more specific help that way.

In general, you should strive for proper normalization of your database. This normally means more tables, and the tables are thin, not wide. However, if you have similar sets of data that can be "typed" and placed into the same table, that is normally a good route to take. For example, if you have a business phone numbers and home phone numbers, I would recommend putting these in the same table and adding a field to specify its type, rather than having two separate tables. On the other hand, you shouldn't try to squeeze more disparate types of data into the same table and type them.

|||

By objects i mean like applications, users, groups.
By subobjects i mean phonenumbers, emails, contacts(for example when an application have a number of contacts linked to it) etc

To illustrate i post some of my tables:

First an object table:

CREATE TABLE [sw20aut].[sw_apps] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[kund_id] [int] NOT NULL ,
[namn] [varchar] (80) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[beskrivning] [text] COLLATE Finnish_Swedish_CI_AS NOT NULL DEFAULT ('') ,
[usr_rubrik] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL DEFAULT ('Övrig information') ,
[usr_information] [text] COLLATE Finnish_Swedish_CI_AS NOT NULL DEFAULT ('') ,
[kat] [int] NOT NULL DEFAULT('0') ,
[typ] [tinyint] NOT NULL DEFAULT('0') ,
[skapare] [varchar] (15) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[skapad] [datetime] NOT NULL ,
[del] [bit] NOT NULL DEFAULT ('0')
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Here are some subtables:

CREATE TABLE [sw20aut].[sw_sub_email] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[p_typ] [varchar] (3) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[p_id] [int] NOT NULL ,
[epost] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NULL ,
[kommentar] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [sw20aut].[sw_sub_phone] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[p_typ] [varchar] (3) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[p_id] [int] NOT NULL ,
[tel_land] [varchar] (6) COLLATE Finnish_Swedish_CI_AS NULL ,
[tel_rikt] [varchar] (6) COLLATE Finnish_Swedish_CI_AS NULL ,
[tel_nr] [varchar] (30) COLLATE Finnish_Swedish_CI_AS NULL ,
[kommentar] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [sw20aut].[sw_sub_contacts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[p_typ] [varchar] (3) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[p_id] [int] NOT NULL ,
[kon_id] [int] NOT NULL ,
[kon_kid] [int] NOT NULL ,
[kommentar] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [sw20aut].[sw_sub_grupper] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[p_typ] [varchar] (3) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
[p_id] [int] NOT NULL ,
[grp_id] [int] NOT NULL ,
[grp_kid] [int] NOT NULL ,
[kommentar] [varchar] (60) COLLATE Finnish_Swedish_CI_AS NOT NULL
) ON [PRIMARY]

p_id = the parents id
p_typ = type of parent, for example app, con or grp

As seen above i could join sub_contacts and sub_groups easily(need another field to set if its a contact or group though) and i could probably join email and phone as well, in worst case using some kind of commaseparated format to separate countrycode, areacode etc. I have a couple of other similar sub tables as well The question is if it could be worth the effort or the larger tables(more rows) and another field to sort by would negate the advantage of fewer querys when listing? Practically it would mean a step from 6-7 querys on worst pages down to 3-4.
I use batched dynamic querys and some stored procs depending on circumstances

|||

The baseline for the table definition is files and association so if what I am seeing is correct you have maybe one or two secondary tables and the relationship is actually quantified by upper and lower bound Cardinality meaning number of expected rows. And I don't think name should be VarChar 80 more like 30-50. Try the links below for functional dependency and free tables you can clone. Hope this helps.

http://www.databaseanswers.org/data_models/

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

|||

The problem regarding sizeing the name columns in different tables are partly because i will have to import data from old tables used by the former application.

These tables are very poorly designed(even to my standards ;) ) and have caused users to add more than just name in the namefields so forth. I will control this better programatically but
nevertheless stripping of existing text in the field would not be popular so my thought weher to implement it and try steering all new information in a better way.
I think that this meant i needed that size when examining the old data to prevent truncation.

Anyway the tables above are examples i have more secondary tables and quite a few primary tables as well.
The basic concept i was thinking about if its better practice to have larger tables wich results in less querys / page (merging the sub/secondary tables as much as practically possible) or using smaller, slicker tables and more inpage querys/procs. For example using 1 table to link both contacts and groups and 1 table for perhaps phone/email and even links as long as one can reuse the fields not resulting in empty fields.

Shall check your links btw

|||

You could clean the data before importing it and I am concerned you are describing tables on the Calculus end where you have main table and the dimenssions on this end tables sizes should be similar. I know you can use validators but you can also use CHECK CONSTRAINT on you columns so people cannot insert whatever they like in name columns.

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

Thursday, February 16, 2012

Beginning Balance Query

Hello all,
I would like a total (beginning balance) field in a query be the total for
the year up to the month based on another field within the query, so that
when the fiscal_date = '5/1/2006' that I would have a field contain totals
from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I am
trying to populate a table with 6 years of data that would have a beginning
balance field and then todays current amount based off of a Fiscal_Date fiel
d.
My problem is I cannot figure out how to do a sum of prior data based off of
another fields current date. Am I trying to do too much in one query to
populate the table?Lyners wrote:
> Hello all,
> I would like a total (beginning balance) field in a query be the total for
> the year up to the month based on another field within the query, so that
> when the fiscal_date = '5/1/2006' that I would have a field contain totals
> from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I
am
> trying to populate a table with 6 years of data that would have a beginnin
g
> balance field and then todays current amount based off of a Fiscal_Date fi
eld.
> My problem is I cannot figure out how to do a sum of prior data based off
of
> another fields current date. Am I trying to do too much in one query to
> populate the table?
>
Without DDL, I don't know your actual table/field names, but something
like this should be close:
SELECT
Table2.FiscalDate,
(SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) =
DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) <
DATEPART(month, Table2.FiscalDate)) AS FiscalAmount
FROM Table2|||Thank you Tracy,
Yes, I did something close to this. What I ended up doing was create 2
tablkes that were identical, and then used a cross join where one of my
criteria is that a.date < b.date, then in the select statement I added a
sum(a.amount). This way I sum all a.amount that is less than b.date.
This way I have all of the b table fields and the total of the amount from
the a table all together.
Thank you,
Lyners
"Tracy McKibben" wrote:

> Lyners wrote:
> Without DDL, I don't know your actual table/field names, but something
> like this should be close:
> SELECT
> Table2.FiscalDate,
> (SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) =
> DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) <
> DATEPART(month, Table2.FiscalDate)) AS FiscalAmount
> FROM Table2
>|||Why would you create a second identical table when a VIEW would most likely
have worked -without the issues of keeping 2 tables syncronized?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Lyners" <Lyners@.discussions.microsoft.com> wrote in message
news:7FA08953-C425-43CD-A198-3CBC08EA229E@.microsoft.com...
> Thank you Tracy,
> Yes, I did something close to this. What I ended up doing was create 2
> tablkes that were identical, and then used a cross join where one of my
> criteria is that a.date < b.date, then in the select statement I added a
> sum(a.amount). This way I sum all a.amount that is less than b.date.
> This way I have all of the b table fields and the total of the amount from
> the a table all together.
> Thank you,
> Lyners
> "Tracy McKibben" wrote:
>|||Good point Arnie. I actually just created a large query that has 2 identical
select statements. The second select statement I total the amount field base
d
on the cross join where clause. I am in the design phase of this report, so
in the future, I will most likely create a view (faster) of the select query
and just reference that instead of requerying everytime.
Thanks for the performance pointer.
"Arnie Rowland" wrote:

> Why would you create a second identical table when a VIEW would most likel
y
> have worked -without the issues of keeping 2 tables syncronized?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Lyners" <Lyners@.discussions.microsoft.com> wrote in message
> news:7FA08953-C425-43CD-A198-3CBC08EA229E@.microsoft.com...
>
>

Monday, February 13, 2012

Beginner Q: Pocket PC access to SQL Server 2005

Hi,

I am a total beginner to Pocket PC, and a 2-month beginner to SQL Server 2005 and Visual Studio 2005. I made a database Pillbox.mdf, and wrote a Windows Forms program to access it. It's really easy. I just added a Data Source, chose tables and Stored Procedures, and dragged them onto my forms. It automatically created adapter classes and a DataGrid for me.

Next, I wanted to do the same thing for Pocket PC. I did the same drag and drop thing, and was happy to see the empty DataGrid in the emulator (design-time) form. From here on, trouble. When I ran it, it said it could not connect.

So some questions:

1) Does my connection string have to change? It has 3 parts: machine name, database name, and security spec. It specifies my machine name. Is that ok? Is it known by the Pocket PC, or do I have to specify http something? Does my machine have to change any settings to become a "server"? It's just a standalone PC with no network. I understand the emulator chooses to pretend that it's not "on" my computer. Is the database name known by the Pocket PC? (Same type of question.) And is Windows authentication ok on a Pocket PC? Or must I create a name/password pair on SQL server?

2) When I went to modify my connection string, I noticed a setting to specify the Data source. For SQL Server 2005, it asks me for a dbo, but for SQL Server Mobile, it asks me for a *.sdf file. What type of file is this? Is there a way to generate this from my other database? Is it not possible to have the the Pocket PC app point to the same database as the Windows app? Oh... does the sdf represent the Pocket PC's local database? If so, then my question is just how to populate it. But I will also have to update the main database at some point. I guess I'm confused about how Pocket PC's are supposed to work.

3) Do I have to deploy SQL CE or something? I think I read that Visual Studio does it automatically. It's a bit hard reading blogs because many comments talk about things that are now contained in VS. One blog was talking about 7 dlls that need to be downloaded to the Pocket PC.

4) My application just has to get a table, store it on the Pocket PC, and allow the user to modify and update records. The bandwidth should be low, as the table is small and updates should not be frequent. Is this a reasonable design? What I am asking is if it's ok to have direct database access, or if I should package up the data in some custom format, and use a Web service to pass the data back and forth. I have read and understood articles why you should not pass a Dataset in a web service, but the point was just that web services can be interoperable, so why spoil that with MS-only Datasets. In my case, I just want to get this simple app up and running quickly.

Thanks in advance.

[Excuse me for the late response]

Make sure of the following:

1) You have Remote Connetions enabled to SQL Server 2005. Please use Surface Area Configuration and enable TCP/IP and Named Pipes.

2) You do not need SQL CE/Mobile. If you are having app directly to work with SQL Server 2005. However, here the assumption is that you use app only when you have connection to SQL Server 2005.

However, I have some questions for you:

1) You said your app needs to get the table from SQL Server 2005 and store it on device. But in what form you want to store. That is where SQL Mobile helps you. SQL Mobile is a tiny SQL database management on devices. Please go through http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295034&SiteID=1 and you will have lots of information to know about SQL Mobile.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Beginner Q: Pocket PC access to SQL Server 2005

Hi,

I am a total beginner to Pocket PC, and a 2-month beginner to SQL Server 2005 and Visual Studio 2005. I made a database Pillbox.mdf, and wrote a Windows Forms program to access it. It's really easy. I just added a Data Source, chose tables and Stored Procedures, and dragged them onto my forms. It automatically created adapter classes and a DataGrid for me.

Next, I wanted to do the same thing for Pocket PC. I did the same drag and drop thing, and was happy to see the empty DataGrid in the emulator (design-time) form. From here on, trouble. When I ran it, it said it could not connect.

So some questions:

1) Does my connection string have to change? It has 3 parts: machine name, database name, and security spec. It specifies my machine name. Is that ok? Is it known by the Pocket PC, or do I have to specify http something? Does my machine have to change any settings to become a "server"? It's just a standalone PC with no network. I understand the emulator chooses to pretend that it's not "on" my computer. Is the database name known by the Pocket PC? (Same type of question.) And is Windows authentication ok on a Pocket PC? Or must I create a name/password pair on SQL server?

2) When I went to modify my connection string, I noticed a setting to specify the Data source. For SQL Server 2005, it asks me for a dbo, but for SQL Server Mobile, it asks me for a *.sdf file. What type of file is this? Is there a way to generate this from my other database? Is it not possible to have the the Pocket PC app point to the same database as the Windows app? Oh... does the sdf represent the Pocket PC's local database? If so, then my question is just how to populate it. But I will also have to update the main database at some point. I guess I'm confused about how Pocket PC's are supposed to work.

3) Do I have to deploy SQL CE or something? I think I read that Visual Studio does it automatically. It's a bit hard reading blogs because many comments talk about things that are now contained in VS. One blog was talking about 7 dlls that need to be downloaded to the Pocket PC.

4) My application just has to get a table, store it on the Pocket PC, and allow the user to modify and update records. The bandwidth should be low, as the table is small and updates should not be frequent. Is this a reasonable design? What I am asking is if it's ok to have direct database access, or if I should package up the data in some custom format, and use a Web service to pass the data back and forth. I have read and understood articles why you should not pass a Dataset in a web service, but the point was just that web services can be interoperable, so why spoil that with MS-only Datasets. In my case, I just want to get this simple app up and running quickly.

Thanks in advance.

[Excuse me for the late response]

Make sure of the following:

1) You have Remote Connetions enabled to SQL Server 2005. Please use Surface Area Configuration and enable TCP/IP and Named Pipes.

2) You do not need SQL CE/Mobile. If you are having app directly to work with SQL Server 2005. However, here the assumption is that you use app only when you have connection to SQL Server 2005.

However, I have some questions for you:

1) You said your app needs to get the table from SQL Server 2005 and store it on device. But in what form you want to store. That is where SQL Mobile helps you. SQL Mobile is a tiny SQL database management on devices. Please go through http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295034&SiteID=1 and you will have lots of information to know about SQL Mobile.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation