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

Best design for a service that will monitor db

We have an existing database that is constantly receiving updated
information on the status and attributes of specific objects within the
application in batches. As these records come in, there is portions of the
table that they populate that are intentionally left empty, because the data
for these fields is retrieved from a seperate Java application through a
published web service (on same network). We are constructing a .Net service
which will handle the retrieval of records from the Java app and push the
new data into the relevant fields.
We are currently designing the .NET service to check the database on a
predefined interval, to see if any new records have appeared that need to be
looked up in the Java application. However, it would be preferable (at
least for testing) if this interaction could be designed so that the Sql
Server 2000 database could notify the .Net service that a new batch of
records has arrived (push instead of pull). Does anyone have any knowledge
if there is a means by which this can be accomplished?
Thanks.Hmmm ... There surely is a notificaiton service in SQL Server but as far as
I've read the documentation it maynot be suitable for this problem ...
But I think you can write a trigger that can call a DTS package or a Jobs
framework to do this notification ... I think this can also be one solution
...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"nfalconer" <navid@.gci.net> wrote in message
news:vm4o27o29cu596@.corp.supernews.com...
> We have an existing database that is constantly receiving updated
> information on the status and attributes of specific objects within the
> application in batches. As these records come in, there is portions of
the
> table that they populate that are intentionally left empty, because the
data
> for these fields is retrieved from a seperate Java application through a
> published web service (on same network). We are constructing a .Net
service
> which will handle the retrieval of records from the Java app and push the
> new data into the relevant fields.
> We are currently designing the .NET service to check the database on a
> predefined interval, to see if any new records have appeared that need to
be
> looked up in the Java application. However, it would be preferable (at
> least for testing) if this interaction could be designed so that the Sql
> Server 2000 database could notify the .Net service that a new batch of
> records has arrived (push instead of pull). Does anyone have any
knowledge
> if there is a means by which this can be accomplished?
> Thanks.
>

Best approach for creating SQL Server 2005 reports to run against SQL 2000 database engine

We have a client who is running SQL Server 2000. They have Business Objects reports. We want to create those same reports using SQL Reporting Services and get rid of Business Objects. The trick is, we'd prefer to create them in SQL Server 2005 with VS 2005, so that we have the reports created with the newest version rather than having to use the older, less functional version to create them. What are our options here? Can we create the reports in 2005 and somehow run the reports against a 2000 database without making our client have to install a SQL Server 2005 instance?
Or will we need to create the reports in SQL Server 2000? Are there other combinations that would work where we can still create them in 2005 but run against 2000?
I'm sure they will upgrade eventually, but I'd rather not rush them at this time.
Thanks much!I think I didn′t get you right, so here are two explanations to your problem:

You can run reports against every supported datasource, this does not have to be SQL Server, it can either be Oracle, a text file, a Webservice or any other source. You can use the out-of-the-box functionality and sources or you can create your own custom provider which leaves with unlimited variety for sources. The Report Server has to be installed on a SQL Server 2005 Server. Therefore you need a SQL Server 2005 licence. If you have one of these you can also install a SQL Server 2005 database. If the clients don′t have a SQL Server 2005 licences you will be stuck to SQL Server Express Advanced Edition, which is capable to distribute it as you want but will only have access to local SQL Server database sources.

So I hope I hit on of your problems withthat answer :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks - I think that does answer my question. My customer only has SQL Server 2000 right now, so it seems I will need to build the reports in SQL Server 2000 using Visual Studio 2003 as the IDE. In our development shop we have moved on to VS 2005, so I am assuming that for new clients we can upgrade our reports to 2005 without too much effort. If you know of any issues about upgrading reports from SQL Server 2000 reporting services to SQL Server 2005, please let me know.

Thanks much!