Help in setting up tables - MS SQL

[MB]

I need to develop a Cold Fusion application using SQL tables, I am not sure
how to setup my tables or that this is the optimal way of setting my tables
for the application that I am trying top automate.

I've first created 2 tables as follows:

SOSC
-----
1 Record ID (Primary key)
2 CMP_TIME_ID (Foreign key)
....
8 item 8

SOSCF

-----
1 CMP_TIME_ID (Primary key)
2 FAX_TIME
3 FAXED_BY

This is my first foray into creating primary/foreign keys, linking tables,
utilizing SQL. (I am not very sure the above was a good idea so I unified
the two tables, and got rid of the foreign key CMP_TIME_ID. If some kind
person that utilizes SQL and CF on a daily basis would be of help it would
greatly help me get up the speed on both SQL as well as CF.

SOSC
-----
1 Record ID (Primary key)
2 FAX_TIME
3 FAXED_BY
4 SO
....
11 item 11

This will be an intranet web based page where end users are adding to the
above table information about a task (service order) that they are
completing. The completion needs to be faxed to customer and the requirement
is to fax completion as they are being completed in the following agreed to
times, 10AM, 1PM, 4PM, 5PM, 8:30PM and 10:30PM.

I hope I did the right thing in getting rid of the second table, I think
this will make my application easier to develop, and easier to maintain.
Also I believe the queries will be easier to develop since I may want to be
able to look at any given fax time, that is look up all the service orders
that were completed for a given fax time.

[John Bell]

Hi

It is usually best to post DDL (Create Table statements etc) as generated by
the scripting options in Enterprise Manager or Quary Analysers Object
Browser as this leads to less ambiguity.

What you have done is de-normalise your database. IMO it is generally better
to have a correctly normalised database as this reduces duplication of data
and possible integrity problems. It is possible that de-normalisation will
improve speed, but in a lot of cases the original schema is not fully
optimised and that is why it does not perform well.

An index on the column CMP_TIME_ID in SOSC would improve performance of FK
lookups. If you ran your application and used profiler you would be able to
see what statements are being executed against your database. You can see if
the queries are being cached and if the SQL is optimal. Looking at query
plans for queries that are slow or called often may give you some idea on
how they can be improved. Also it is worth looking at the Index Tuning
Wizard to see if that may suggest anything.

Re-indexing fragmented indexes will improve performance as well as
up-to-date statistics will help.

You may want to look at
http://msdn.microsoft.com/library/de..._perf_3h9h.asp

http://msdn.microsoft.com/library/de...asp?frame=true

John


"MB" <mb****@ameritech.net> wrote in message
news:LN*********************@newssrv26.news.prodig y.com...

I need to develop a Cold Fusion application using SQL tables, I am not sure how to setup my tables or that this is the optimal way of setting my tables for the application that I am trying top automate.

I've first created 2 tables as follows:

SOSC
-----
1 Record ID (Primary key)
2 CMP_TIME_ID (Foreign key)
...
8 item 8

SOSCF

-----
1 CMP_TIME_ID (Primary key)
2 FAX_TIME
3 FAXED_BY

This is my first foray into creating primary/foreign keys, linking tables,
utilizing SQL. (I am not very sure the above was a good idea so I unified
the two tables, and got rid of the foreign key CMP_TIME_ID. If some kind
person that utilizes SQL and CF on a daily basis would be of help it would
greatly help me get up the speed on both SQL as well as CF.

SOSC
-----
1 Record ID (Primary key)
2 FAX_TIME
3 FAXED_BY
4 SO
...
11 item 11

This will be an intranet web based page where end users are adding to the
above table information about a task (service order) that they are
completing. The completion needs to be faxed to customer and the requirement is to fax completion as they are being completed in the following agreed to times, 10AM, 1PM, 4PM, 5PM, 8:30PM and 10:30PM.

I hope I did the right thing in getting rid of the second table, I think
this will make my application easier to develop, and easier to maintain.
Also I believe the queries will be easier to develop since I may want to be able to look at any given fax time, that is look up all the service orders
that were completed for a given fax time.