Finding The Natural Key

Mar 15, 2006

Whenever I want help on a query, I get told my design is wrong, So this
time I'm posting a message during the design phase: How am I going to
perfectly design the following?

We want to be able to track time for users for multiple modules, for
now a Schedule module and a Punchlist module. These modules already
exist and there are dozens of other modules which we will add to the
list as well, two or three at a time - so it should be possibly to add
to the list of related modules fairly easily.

In my mind the natural key is the UserOrContactId (an id for a
particular person), the date the time is for and ReferenceId to the
module it is in, either PunchlistItemId or ScheduleTaskId. I haven't
done the foreign keys yet and will do that once the table design is
settled. The MinutesSpent is going to be the minutes the person spent
doing a particular schedule task or punchlist item, which will be
converted from hours and minutes to just minutes for the database with
a constraint for total miuntes per day not being more than 24 hours.
Create and Update User/Time is a standard thing we put on all our
tables for auditing purposes. The ForDate will be the date the hours
happen so if the natural key is done right that will take care of
checking that two entries aren't made on the same day, i.e. the total
horus should just be updated if an entry exists.

The problem is that I can't make the natural key a primary key because
you can't have nulls in the primary key, i.e. it will be either a
PunchlistItem OR a Schedule Task, but not both for a particular record.
In the past I have done something like RefType = 'Schedule', RefId =
ScheduleTaskId, but then foreign keys can't be applied.

So what's the perfect solution so that I won't get heckled for design
when I ask a query question about it in a few months? Celko, get it all
out of your system - I don't understand database design, blah, blah,
blah, but show me the perfect solution.

CREATE TABLE [dbo].[TimeSpent](
[UserOrContactId] [int] NOT NULL,
[ForDate] [smalldatetime] NOT NULL,
[PunchlistItemId] [int] NULL,
[ScheduleTaskId] [int] NULL,
[MinutesSpent] [int] NOT NULL,
[Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreateUser] [int] NOT NULL,
[CreateTime] [smalldatetime] NOT NULL,
[UpdateUser] [int] NOT NULL,
[UpdateTime] [smalldatetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[TimeSpent] WITH CHECK ADD CONSTRAINT [CK_RefIds]
CHECK (([PunchlistItemId] IS NOT NULL OR [ScheduleTaskId] IS NOT
NULL))
GO

View 13 Replies


ADVERTISEMENT

Connection Driver NATURAL

Mar 22, 2004

does anybody know where could i find a driver which would be able to connect database with BCK extension (as i know this is database written in natural language) from SQL Server 2000

View 1 Replies View Related

What Are Natural And Equi Joins?

Sep 24, 2007

what are natural and equi joins?

View 16 Replies View Related

SQL-Question: Natural Join With Condition

Nov 10, 2007

 Hello! I have two tablestable1:                     table2:person                     Manager----------                     -----------------*id                           *id*Manager_id             *Name*Branch                   *Title Now I want to select person.* and Mangager.Name, where person.Manager_id=Manager.id.Ok, that would be easy for me, but the point is, that the column person.Manager_id might be empty or =0 (as integer), and in that case I want to have the person.* data too.So, something like Select person.*, Manager.id From person, Manager Where person.id=4 AND person.Manager=id=Manager.id doesn't work. Can somebody explain me how to fix it? I'm using MS-SQL2000 if it is important.... Best regards! 

View 1 Replies View Related

Database Written In Natural And Bck Data Format

Mar 9, 2004

does anybody know, is there any possible way to connect to database written in Natural ?? After evaluating everyday backups on that database all i can see is BCK data format. im trying to create datawarehouse running on sql server 2000 and gather all operational data from the systems that are working in my firm. I just managed to get data originating from Interbase and these with dbf and txt format . unfortunatelly my problem with bck data format is still open.
As you can see im a beginner with that issue so if anybody could help i would be very gratefull.

View 1 Replies View Related

SQL Server 2008 :: Put Clustered Index On 8 Column Natural Key Or On Identity Key

Aug 2, 2015

I am extremely new to database design, and I ran into a problem that I know comes up often, however has many opinions...

Basically I have a table that is going to have 50+ columns. The natural key on this table is actually 8 columns wide, 4 of them being Varchar columns by default. (varchar(50)'s).

I have added an identity column, (1,1) to the table, however I put the clustered index on the 8 natural keys... My plan is to rebuild the clustered index once nightly when the system isn't in use (after 7 pm).

I know others would say it would be better to have the clustered key on the 1,1 column and then add indexes on the other 8 fields... However I don't quite understand why honestly...

Every single query against this table will use the 8 columns, and will NOT use the Identity column (1,1) because they are calls from other systems that do not know the Identity column....

Therefore if your database is set up for query speed, and every single query has to have a value for 8 columns to get a valid result, does it make sense to put a clustered index over the 8 columns?

If not why? Why is putting a clustered index on an identity column (that will literally never be used in a query) a better solution?

View 9 Replies View Related

Finding Something In SQL

Jun 22, 2007

Hello,

I am very new to SQL and I have an issue. We have a website that has pictures on the site. We want to take them down but the pictures are somehow tied into the database, they aren't on any of the servers. How can I find these pictures that seem to be somehow hard coded into the database?

Any help would be greatly appreciated.

View 2 Replies View Related

Finding Right Row

Mar 11, 2008

i have

create table test

(id_text int
,text_name varchar(10)
,id_author int)

insert into test (id_text, text_name, id_author) values (205, 'name1', 123)
insert into test (id_text, text_name, id_author) values (205, 'name2', 124)
insert into test (id_text, text_name, id_author) values (205, 'name2', 125)
insert into test (id_text, text_name, id_author) values (206, 'name2', 124)
insert into test (id_text, text_name, id_author) values (207, 'name2', 122)
insert into test (id_text, text_name, id_author) values (207, 'name2', 128)

select id_text, id_author from test

and i need to get out only id_text with the first author.
any solutions for sql server 2000?

thank you

View 6 Replies View Related

Finding The Max Value

Aug 16, 2006

I have a report that is grouped by week. The details of the week have the date, time interval (half hour), and the maximum value for that day. So in the example below I am showing the week of May 21st. Then I have all of the days of that week, sorted by date as requested, with the halfhour the maximum value occured in on that day. So Monday the 22nd has the maximum value for the week at 254 (and is highlighted in red). Where the ????? are below, I need to display '1400' which is the interval, but have not yet been able to figure out how to do this.

I tried the following expression:
=iif(max(Fields!MaxValue.Value) = Fields!MaxValue.Value, Fields!Interva.Value, "X")

but this only works if the first day is the maximum value.





5/21/2006



?????
254

Sun, 5/21/2006
1000
147

Mon, 5/22/2006
1400
254

Tue, 5/23/2006
1830
141

Wed, 5/24/2006
1830
107

Thu, 5/25/2006
0830
210

Fri, 5/26/2006
1900
36

Sat, 5/27/2006
1000
149







Any ideas are greatly appreciated!

View 11 Replies View Related

Finding The Next Record

Apr 26, 2007

I have, say 4 rows for 1 client I want to find the date difference between two(or more) of the rows when the first has a true flag and the next does not
Row1  Date1,falseRow2  Date2,trueRow3  Date3,falseRow4  Date4,false
so I want to find the difference between Row2 Date2 and Row3 Date4I know I can loop through the rows but with 000's of clients each with x numbers of rows this will surely be very slow 
 

View 21 Replies View Related

Finding Data

Feb 2, 2008

I have a form where users can enter an appointment on this form some personal details are entered. I want the users to be able to write in a textbox the users surname. they will then press a button next to the textbox that will bring up a smaller screen displaying that users details :
SELECT * FROM Members where surname = (textbox1)
Alternatively the users can click the button and bring up a screen where the users can put the surname into a textbox and then bring up the users details this way.
I am not sure how to do this or how to select from the database where a field in the database is the same as the information put in to a text.
Is this possible? Thanks in advance Mike

View 1 Replies View Related

Finding SQL Triggers

Feb 29, 2008

I have a table TEST.
 I want to find the triggers which use this table test.
For example: If there is a table A, and a insert in A causes a trigger to insert data in TEST,
I want to find this particular trigger.
Any ideas?
I am using SQL server 2000
thank you

View 4 Replies View Related

Finding The Unique....

Aug 28, 2001

hai
I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table
structure.
Thanks in advance
satya

View 1 Replies View Related

Finding Duplicates In SQL

Jul 26, 2002

I have a customer database with the following structure:

Dept ID (int)
Section (varchar)

I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.

Example Data:

Dept Section
1 Admin
1 Finance
1 Marketing
2 Admin
2 Sales
2 Marketing
3 Admin
3 Sales
3 Finance

The query would only return 'Admin' since this is the only Section that is represented in Departments 1,2 + 3.

I am not in control of the data design and can't create a bitwise field to achieve this.

tia,
Steve

View 1 Replies View Related

Finding Suid

Oct 18, 1999

How can I find out a user's suid or login name from within a stored procedure?

View 1 Replies View Related

Finding The First Distinct Value

Sep 8, 2004

Hi

My problem is a little tricky. I am trying to get the first distinct row(s) from a table where one column will have only a few differences, while a second is unique.

Now using "distinct" doesnt help as even though distinct(column1) may return only a few rows, because the next column is unique you will still have all rows returned.

How do I return only 1 row per distinct(column1) and drop the rest? What makes this so annoying is that all columns need to be returned, but only in accordance to distinct(column1)

View 1 Replies View Related

Finding Relationships

Sep 23, 2005

Hi, sorry if this comes across as a stupid question, but i'm new to SQL. I have had a look on Google and on these forums but not found the answer..
I am running MS SQL 7 with a database containing around 130 tables.. i want to know if there is a query/command that i can type that will look through all the tables and tell me which ones reference a specified table (through it's key). Hope this makes sense! i've looked at some of the stored procedures but not found any that give me anything useful.

EDIT: Just so you are aware, i did not create this database, hence why i am not aware of the relationships myself, i have only just started working on it.

Thanks
Steve

View 2 Replies View Related

Finding The Numbers

Jul 8, 2005

I have a column of data of type varchar. it contains a mix of text id's and numeric id's.
all i want is the highest purely numeric value
The closest i can get is to use a regex expression, but it don;t work and i just know there is a better way of doing it.

SELECT * FROM customers WHERE custref LIKE 'd*d';

I am using MS SQL 7

thanks so much for any help

View 4 Replies View Related

Finding The Table Name.

Dec 15, 2006

I have a script that creates these spin off tables (used for lightning fast access) on a near daily basis. But it is possible the table does not exist for that day - in which the script need to check one day back - so on and so forth until finding the most recently created table.

The script I have written fails with overflow error.
I was hoping someone could tell me the correct syntax to accomplish this and also why i recieve this overflow error...


Code:


Dim str_SQL_Asset_1
daysBack = 1
tblFound = FALSE
Dim xbl_Holdings_date
Dim xMonth
Dim xDay
xbl_Holdings_date = date()
If Len(Month(xbl_Holdings_date))=1 Then
xMonth = "0" & Month(xbl_Holdings_date)
Else
xMonth = Month(xbl_Holdings_date)
End If
If Len(Day(xbl_Holdings_date))=1 Then
xDay = "0" & Day(xbl_Holdings_date)
Else
xDay = Day(xbl_Holdings_date)
End If

DO WHILE tblFound = FALSE
xbl_Holdings_Name = "xbl_Holdings_" & Year(xbl_Holdings_date) & "_" & xMonth & "_" & xDay
SQL_tblCheck_STR = "SELECT * FROM INFORMATION_SCHEMA.TABLES " & _
"WHERE table_name = '" & xbl_Holdings_Name & "' "

Set MM_checkCmd = Server.CreateObject("ADODB.Recordset")
MM_checkCmd.ActiveConnection = conn_str_ClientDB
MM_checkCmd.Source = SQL_tblCheck_STR
MM_checkCmd.CursorType = 0
MM_checkCmd.CursorLocation = 2
MM_checkCmd.LockType = 3
MM_checkCmd.Open()


IF MM_checkCmd.EOF Then
xbl_Holdings_date = (xbl_Holdings_date - daysBack)
daysBack = daysBack + 1
tblFound = FALSE
ELSE
tblFound = TRUE
MM_checkCmd.CLOSE()
SET MM_checkCmd = Nothing
END IF
loop



The goal of this snippet is simply to identify and set the value for the existing table name.
An example table name would be "xbl_Holdings_2006_12_10".

I could have posted this question in the VB forum but I would like to push as much of the work onto SQL as possible and also - this sort of operation is more likely to be required of a dba imo.

View 1 Replies View Related

Finding Duplicates

Jul 7, 2004

I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)

thank you

SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups
FROM TERMINATION
GROUP BY [TM#]
HAVING Count([TM#])>1;
GO

View 14 Replies View Related

Finding Row Size

Oct 7, 2004

Hi,
Anyone got any ideas how to calculate the occupied row size of a table?

View 1 Replies View Related

Help Finding The Max Total

Dec 3, 2004

Hi,

I have the following code

SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2



It prints out:
Wbs2 BillExt
0141 0
0143 0
1217 20580

I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.

Can someone help me with this?

Thanks.
laura

View 10 Replies View Related

Finding Last Entries

Jan 27, 2005

Hi, I am searching for the most easy SQL solution:

Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?

Thx for reply.

dajm

View 11 Replies View Related

Finding Duplicates - Is This Right?

May 16, 2007

I've done a search and I THINK I've got my head round this, but I'd be very grateful if someone could reassure me:

SELECT Email FROM List1 WHERE EXISTS (
SELECT Email FROM List2 WHERE List2.Email= List1.Email
) AND List1.Email <> '44'

That will give me every email address from list one that (a) appears in list two, and (b) isn't '44'. Right?

And to find all the emails from List1 that DON'T occur in List2 (and aren't '44'), I just put "NOT" in front of "EXISTS". Right?

Sorry for asking an obvious question but I'm having a real mental block here. :o

View 6 Replies View Related

Finding Some Tables

Feb 8, 2008

I have many tables in one database, and some of them are empty. How can I list / find (for eg. names) only non-empty tables?

View 2 Replies View Related

Finding A Set Of Data Not In Another Set

Apr 15, 2013

Our products exist in batches. These batches reference a warehouse.Each warehouse carries a set of 'roles'.Users on our system also carry 'roles'. Comparing user and warehouse roles tells the system what warehouses a user can access.

So, if:

WAREHOUSE1 has roles A + B
WAREHOUSE 2 has role B
WAREHOUSE 3 has roles A + C
and
USER1 has role A
USER2 has roles A + B + C
USER3 has role C
then
USER1 will have access to WAREHOUSE1 and WAREHOUSE3
USER2 will have access to all 3 warehouses
USER3 will have access to WAREHOUSE3.

What I need to do is write some SQL which allows a part number to be entered, and a list of batches for that part to be shown with a list of users who *CANNOT* access that part - because they do not have the relevant 'roles' for the warehouse(s) the part is in.

I can do the *opposite* of this quite easily (ie: show users who HAVE got access):

select distinct sb.batchnumber, ur.userid
from stockbatches sb
inner join warehouseroles wr on sb.warehouseid = wr.warehouseid
inner join user_roles ur on wr.roleid = ur.roleid where sb.partid = @part
order by sb.batchnumber, ur.userid

- it's a straightforward join between the warehouse and user roles, with a 'distinct' to eliminate any duplications.

Fair enough, but it's not what I want.

Using the role data above, with the following batch info:

Batch 1000, warehouse WAREHOUSE1
Batch 1001, warehouse WAREHOUSE2
Batch 1002, warehouse WAREHOUSE3

I actually want to get this results set:

Batch Users who can't use this batch
1000 USER3
1001 USER1
1001 USER3

I'm certain I can write the necessary logic programmatically in T-SQL, looking for single-statement SQL call?

View 2 Replies View Related

Finding Out Indexes

Feb 26, 2004

Hi All,

Sql server 7

Is there anyway of listing the indexes that are present in a database.


TIA
Adil

View 1 Replies View Related

Finding A View Or Sp

Mar 19, 2004

According to a developer that worked on my db, there is a view :
xvw_CheckVoucherReport

I cant see it is there any way i can find out it exsist and if so what database on my server it is in?

View 4 Replies View Related

Help With Finding Out Latest Changes

Apr 8, 2008

So here is my dilemma:

We have licensed software from a 3rd party and we do not have the source code, nor can we do anything to change the database schema, write our own procs, triggers, etc.

With that said, we want to know which records in certain tables have been added or modified since a certain time (for example, in the past 24 hours). However, none of the tables has any datetime or timestamp columns on them for create date, update date, etc.

I tried to look into hacking thru DBCC log (dbname, 2) to see if that would help, what a mess. Using a tool like that from Luminent or Red Gate doesnt help either, because long term this needs to be automated if possible. Does anyone have any ideas on how to do this, or is the answer simply, "Sorry mate, you need to get them to allow you to change the database to add new columns and/or triggers". :)

View 5 Replies View Related

Finding Average

Apr 9, 2008

Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".

I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.

Shifts | Mon_day | Mon_Night | Tue_Day.....
Average | 5.2 | 10.2 | 15.2........

The codes that i tried..

declare @Weekday tinyint, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())

if (@Weekday= 1 and (@hour>= 7 AND @hour<19))
begin
Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average'
SELECT AVG(Sat_Night)
FROM Weekly
end

if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7))
begin
Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average'
SELECT AVG(Sun_Day)
FROM Weekly
end

View 7 Replies View Related

Finding Data

Apr 18, 2008

I need to find the object which holds the value(in column)
Whether it is possible?

suppose i need to find the text 'Goal' in stored procedure then i will give:
select * from syscomments where text like '%goal%'

suppose i need to find the text 'Goal' in table level.any system stored procedure?

Txs in Advance..

View 1 Replies View Related

Finding A Column ??????

May 2, 2008

Hi,
How to find if a column exists in Database Table ?

thanx in advance

View 6 Replies View Related

Finding My Views

May 30, 2008

Hi,

I our firm there are 4 people who are working on a database. I need to edit all of my views. Is there any way I can find out which view was created by me?

Please help me.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved