469,277 Members | 2,214 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,277 developers. It's quick & easy.

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.
Jul 20 '05 #1
1 2279
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.

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mike | last post: by
2 posts views Thread by Simon Verona | last post: by
7 posts views Thread by Richard | last post: by
5 posts views Thread by manmit.walia | last post: by
2 posts views Thread by Takeadoe | last post: by
5 posts views Thread by Sam | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.