|
eddieangel wrote: There will always be a 1:1 relationship between the two tables.
If all the attributes depend on the key[^], then they should be in the same table. Put an index on the fields that you're going to search often 
I are Troll 
|
|
|
|
|
I'd also add to the above comment to create a RealProperty view that queries on only those columns that deal with RealProperty and like wise for the HomeOwner data as well. Just make sure that each view uses the index fields defined at the table level and everything should work fine.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
|
I do believe your data structure is incorrect. Your constraint of 1:1 between owner and property fails when someone own more that 1 property or a property has more than 1 owner or a property is sold (I assume you wish to retain history of the property).
As Piebald suggested a many:many link table is what you need to implement.
What happens if you have a company own the property, now you are in the position of needing an address table so you need a many:many link between owner and address...
Designing a data structure is not a trivial excercise and needs time and effort to get it right. It is guaranteed that you will not get it right the first time. Be prepared to admit an error and redesign ASAP, allowing an incorrect design to persist only deepens the cost of fixing the problem.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I appreciate the insight. Thank you.
|
|
|
|
|
I agree with the statement that I think your reasoning is flawed.
More often then not you are going to see multiple owners to a home (or any other entity) simply because of things like marriage, partnerships, trusts, etc... Also, keeping history now requires that you set up 2 tables that contain the same data linked to a building. Keeping it all in one table and adding an attribute to show current vs. past ownership I think is the better option.
In fact, you are probably really going to want to separate out the table that tracks the data about the building, the tables that track the address data of all the possible owners and then use a join table to crate the linking between the two. That I think is the best way to achieve a clean design. When you consider what else you can store in this third reference table (IE: the data about the sales agent(s) involved, the inspectors used durring the sales, etc...) you start to see how it works cleaner.
|
|
|
|
|
Hi,
For an example, using Sql Server - let's say I have three tables:
Client:
ID
Name
File:
ID
Name
Bytes
ClientID
Data:
ID
DataField
FileID
Each record in Data can be linked back to each Client record through the File Record. I want the DataField value to be unique only per client - so two values of "42" in the DataField is ok as long as they link to different clients.
I could put the ClientID in the Data table, but that would then mean I would need to be sure the ClientID in the related File record always matches.
Can an index be set on the Data table using the DataField field and the Client ID field?
If so, how would you do this? If not, is there an alternative?
Thanks
|
|
|
|
|
Stryder_1 wrote: I want the DataField value to be unique only per client
How about this?
Client
ID
Name
DataField
File
ID
Name
Bytes
ClientID
That's what you asked literally, but not what you'd want;
Client
ID
Name
File
ID
Name
Bytes
DataField
ClientID
With a UNIQUE constraint on DataField and ClientId
I are Troll
modified on Monday, October 18, 2010 3:56 PM
|
|
|
|
|
I think you can add two unique constaint in File..ClientID and Data..FileID to achieve your goal. By your data design, if you want the DataField value to be unique only per client, the infomation in [File] should also unique match one [Client] record.
Client:
ID
Name
File:
ID
Name
Bytes
ClientID <<<
Data:
ID
DataField
FileID <<<
|
|
|
|
|
We have a linked server on SQL 2005 to database on AS400. the linked server is made using Microsoft DB2OLEDB provider.
when we are using the linked server, we are getting the following error:
OLE DB provider "DB2OLEDB" for linked server "AS400PROD" returned message "".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "AS400PROD".
When we restart the SQL server machine, the linked server works fine for about 5 minutes then we receive the same error as described before,
The script of the linked server is :
EXEC master.dbo.sp_addlinkedserver @server = N'AS400PROD', @srvproduct=N'AS400', @provider=N'DB2OLEDB', @datasrc=N'S6595D7F', @provstr=N'Provider=DB2OLEDB;Password=ISA108;Persist Security Info=True;User ID=ASI;Initial Catalog=S6595D7F;Data Source=AS400;Network Address=192.1.1.50;Package Collection=KFILFNB;DBMS Platform=DB2/AS400;Process Binary as Character=True'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400PROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'LOGINRPORT',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'use remote collation', @optvalue=N'true'
|
|
|
|
|
What have you tried so far? A quick search on Google brings up a number of possible explanations and solutions for this. Which of them have you tried?
|
|
|
|
|
What iam sure of is that it is not an issue from SQL server. i have tried to ping the AS400 server and the reply is <1 ms then i wrote a query to check the linked server and it is giving an ok reply b4 it stops suddenly. i have seen soo many possible issues and read articles abt the same error Msg 7303 and i will be trying to update the server with hotfixes. Are there any fixes that came to your mind?
|
|
|
|
|
Hi,
I have a table and one column is there like this
SKU Value
-----------
MKG Ex Du A918731765L
TVN Ex Du A919721354L
NCL Ex Du A901713191L
Bin Ex Du A948606458L
--- -- -- ----------
--- -- -- ----------
etc etc etc(table is having hundreds of data like this and it is a expanding table.So i have to split all of them not only the mentioned data.So a common way to split this column is required.)
I am writing a stored procedure to get this columns as
SK1 SK2 SK3 SK4 SK5
---------------------------------------------------
MKG EX Du A91873176 5L
TVN Ex Du A91972135 4L
etc etc
The values will be always in the above format.
Please help.
Thanks in advance
-- Modified Monday, October 18, 2010 12:20 AM
|
|
|
|
|
Hi,
I suggest you create a split function
CREATE FUNCTION [dbo].[Split]
(
@delimited nvarchar(max),
@delimiter nvarchar(100)
) RETURNS @t TABLE
(
id int identity(1,1),
val nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(15)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
Then you cross apply to your data table, pivot, then manipulate the display data.
Here is an example:
declare @tmp table (data varchar(50))
insert into @tmp (data) values ('MKG Ex Du A918731765L')
insert into @tmp (data) values ('TVN Ex Du A919721354L')
insert into @tmp (data) values ('NCL Ex Du A901713191L')
insert into @tmp (data) values ('Bin Ex Du A948606458L')
select [1] as 'SK1', [2] as 'SK2', [3] as 'SK3', substring([4], 0, 10) as 'SK3', substring([4], 10, 2) as 'SK4'
from
(
select [1] , [2] , [3] , [4]
from (
select * from
@tmp t
cross apply dbo.Split(t.data,' ')
) as sourcetb
pivot
(max(val) for id in ([1],[2],[3],[4])
) as pivottable
) as t
Ryan
|
|
|
|
|
Now that is what I call sending the codz - deserves 5
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi,
I want to combine multiple rows in a single one. Have a look at the following sample. Any body know how to do this....thnx in advance
original table
------------------
FID Code Value1 Value2
1 C1 20 Null
1 C1 Null 10
2 C1 30 Null
2 C1 Null 40
To
--
FID Code Value1 Value2
1 C1 20 10
2 C1 30 40
|
|
|
|
|
SELECT FID,Code,Max(Value1),Max(Value2)
FROM TableName
Group By FID,Code
|
|
|
|
|
Perhaps you should use MIN in any of fields for Value1 or Value2
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
|
You need a pivot [^] table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how is it possible to change master Collation in SQL Server 2008.
|
|
|
|
|
If you are referring to change the ms sql server instance's collation refer this link:
http://msdn.microsoft.com/en-us/library/ms179254.aspx[^]
But remember to "detach" user defined databases as they will be dropped when you execute these steps. You can "reattach" the databases when you are done with changing the collation.
If you want to change the collation of a database alone you could use the alter database command. Check out this link:
http://msdn.microsoft.com/en-us/library/ms174269.aspx[^]
As you said master collation, I guess you mean the server instance's collation. Either ways remember to detach (in case changing the server collation) or take a backup (in case you are using alter database cmd) of user databases.
Cheers,
Karthik
|
|
|
|
|
declare @tbldate table(stdid int, sdate date, eddate date)
insert into @tbldate
values
(100, '20100102', '20100505'),
(101, '20100203', '20100302'
)
i want my result set as
check if day in date is 01 if not then make day as 01 and increment month my 1. i have the solution but thats in 2 temporary table and i want full solution in a single table.
|
|
|
|
|
You mistake is that you are treating a date as a string - lesson 1: A DATE IS NOT A STRING. - learn this lesson now and it will save you a lot of pain in the future.
You can use DATEADD within a loop. You still need to generate the sequential numbers (@day) 1-100, then you can do something like
DECLARE
@Day INT
SET @Day = 1
WHILE @Day < 101
BEGIN
SELECT DATEADD(d,@Day,GETDATE())
SET @Day = @Day+1
END
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
.
.