How to remove 1:1 Relationships

I have an asset schema problem which I'm not too sure how to resolve...

I have an asset table which stores values common to all asset types. I have created seperate asset type tables(VehicleAssetDetail, PropertyAssetDetail, InvestmentAssetDetail) to store their specific details.

This in turn creates 1:1 relationships between "Asset" table and the respective detail tables.

If I were to store all the details in the "Asset" table it would cause a lot of redundancy bearing in my mind there are about four other asset type details I have excluded from the schema.

Are the 1:1 relationships bad? If they are, how do I resolve them without throwing all the columns from the asset details tables into the Asset table?

Attached is a 15Kb gif image of the schema. I'd really appreciate any help with this... :)
Thanks.A lot of your assertions are fals.

Why would a 1:1 create redundant data?

Only reason I would use a 1:1 is if the row length is too long, then I would catergorize the data into appropriate entities.

But remember, the best join, is none|||Hi Brett...
You may have misunderstood my statement...
If all the columns of each Detail table would be placed in the "Asset" table and the Details tables dropped, most of the columns in the "Asset" table would be null, except for the columns which relate to the specific asset which is inserted.

A 1:1 relationship would not create redundancy.
So back to my original question... :)|||This was raised in another thread which may be worth a read

http://www.dbforums.com/showthread.php?t=1622324

Read the last 3 or 4 posts or so and you'll get the answer ;)

How to read in multiple SELECT statements of different return types in T-SQL

HI,
I understand how to use NextResultset() in ADO.NET to return multiple
readers from a stored proc. What if a SELECT returns a scalar and
another returns rows.
Thank you!> What if a SELECT returns a scalar and
> another returns rows.
Let me rephrase above:
"What is a SELECT returns a scalar and another returns a table"
Actually all SELECT statements returns tables (with some obscure exceptions like COMPUTE). So, a
scalar is just a table that happens to have only one column and one row. I.e., you treat is as a
table. The ExecuteScalar method is just something that ADO.NET designers cooked up to make it easier
for us to get the value from a select statement that returns a table with one column and one row.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<webdevaccount@.gmail.com> wrote in message
news:1182811103.414233.319970@.m36g2000hse.googlegroups.com...
> HI,
> I understand how to use NextResultset() in ADO.NET to return multiple
> readers from a stored proc. What if a SELECT returns a scalar and
> another returns rows.
> Thank you!
>

How to read a single row in a strongly types dataset?

I'm using strongly typed datasets in my first ASP.NET 2.0 web application. I come from ASP Classic, not an earlier version of .NET, and feel like I'm in another world. I'm slowly getting my head around datasets, but one thing I can't find any information on is how to read the data in a single record?

I'm not talking about for next loops, or accessing row information as a repeater or other control is filled. I'm talking about reading the data returned by a method that returns a table containing a single record. This is what I have:

Dim BookAdapter As New BooksTableAdapters.BooksBBTableAdapter
Dim organizations As Books.BooksBBDataTable
Dim organization As Books.BooksBBRow

organizations = BookAdapter.GetDataByOneBook(sBookID)

Dim sDropOff As String
Dim sOrganization As String
Dim sContact
For Each organization In organizations
If organization.DropOff = 1 Then
sDropOff = "True"
Else
sDropOff = "False"
End If
sOrganization = organization.Organization
sContact = organization.ContactName
Next

sBody = "Books Listing" & Chr(10) & Chr(10)
sBody = sBody & "Organization: " & sOrganization & Chr(10)
sBody = sBody & "Contact Name: " & sContact & Chr(10)
sBody = sBody & "Email: " & organization.Email & Chr(10)
sBody = sBody & "Phone Number: " & organization.Phone & Chr(10)

I'm using the FOR NEXT, but this is silly since I only have one record. GetDataByOneBook(sBookID) does exactly what is says, it returns a single book with a specific bookID.

Not only is this silly, it doesn't work. Using sContact as an example, it's Dim'd as a string. In the FOR NEXT loop, organization.ContactName has the right value, and it appears to be assigned to sContact correctly, but when I try to use sContact in sBody, I get an error saying that sContact has been used before it is assigned a value. Maybe the variables lose their scope outside the loop? Maybe I could get around this by building sBody inside the loop, but there has to be a better way!

Diane

I believe you want to do something like the following with your code:

1Dim BookAdapterAs New BooksTableAdapters.BooksBBTableAdapter2Dim organizationsAs Books.BooksBBDataTable3Dim organizationAs Books.BooksBBRow45 organizations = BookAdapter.GetDataByOneBook(sBookID)67If (organizations.Count > 0)Then89 sBody ="Books Listing" & Chr(10) & Chr(10)10 sBody = sBody &"Organization: " & organizations[0].Organization & Chr(10)11 sBody = sBody &"Contact Name: " & organizations[0].ContactName & Chr(10)12 sBody = sBody &"Email: " & organizations[0].Email & Chr(10)13 sBody = sBody &"Phone Number: " & organizations[0].Phone & Chr(10)1415End If

I do not have your XSD, so I can not test this.

|||

Thank you!

Diane

How to query a number (street number)...

I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.' 200'
' 21'
' 2005'
sorry for confusion just been a long hard day...|||If your street number column is a string, just use the LIKE operator.
If it is not a string, then it should be. Change it.

Rule Of Thumb: If you don't add it, subtract it, multiply it, or divide it, then it is not a number even if it looks like one.|||DECLARE @.MyChar9 CHAR(9)
SET @.MyChar9 =' 20'
SELECT @.MyChar9,LEFT(LTRIM(@.MyChar9),1)
WHERE LTRIM(@.MyChar9) LIKE '2%'


GW|||Correct me if I'm wrong but...
Using LTRIM and LIKE will ignore any indexes.|||Correct me if I'm wrong but...
Using LTRIM and LIKE will ignore any indexes.I cannot because you are not :)

@.OP - simpler just to write
...
@.MyChar9 LIKE ' 2%'
No indexes used there either but less RSI from all that typing.|||... unless of course there might not be leading spaces in which case ignore.|||... unless of course there might not be leading spaces in which case ignore.
In a right justified char(9) column there may be up to 8 leading spaces. Hence Gwilly's LTRIM().|||In a right justified char(9) column there may be up to 8 leading spaces. Hence Gwilly's LTRIM().But there might be none which was why I said ignore my solution (which is likely wrong).|||LIKE will like totally use the index, if the wildcard comes at like the end or whatever. So like this will do an index seek:

where column like 'abcd%'

But this will like totally gag your server:

where column like '%wxyz'

It's like when you wanna text your friend, and you like only know their last name or whatever, you have to go through each one, since the index is ordered by like the first name, you know.

OK. I will go back to English, now ;-)|||' 200'
' 21'
' 2005'
sorry for confusion just been a long hard day...

How is a char(9) field right justified?