Best Practices Question for Outputting

Hey guys,
Little bit of a newbie question here...I have a database with about 20or so tables in a relational model. I am now working on an outputscheme and had a quick question regarding best practices foroutputting. Would it be best to
1) Set up a view that basically joins all of these tables together, then bind a DataSet/DataTable to it and output as needed?
2) Setup individual views for each table and run through them?
Thanks for the help!
e...
I've never liked creating do-everything views. You'll never get the same performance as you would by just creating individual stored procedures which join the tables you need to get the specific fields and records you need to fulfill each type of query or scenario you have. Unless you have a pretty simple site that doesn't do more than a couple very similar things, it's a lot of overhead that's not needed. Your other question: Why set up a view on one table? Unless you're doing a lot of calculated fields in the view that are derived from underlying fields in the table, that's a waste. Not knowing anything really about your situation, my stock advice is to create a stored procedure for every type of query you'll need. Add parameters as needed, but each proc should fulfill a specific need. Don't try to make a proc too general. They tend to get bigger and more confusing over time when they try to do too many different things.|||One very good thing about views is it reduces redundancies in yourprocedures. I'd personally make a few views of the most commontypes of joins you'd make. I've seen this as a problem with manydatabase driven sites and applications where one table change requiresyou to alter 30 stored procedures, and code for multiple pages. Alot of minor changes can remain minor if you consolidate alittle. It's extremely funny though when you have issues where afield name is spelled ammount. :)

Best Practice for Structuring XML?

I've created a large XML document from a relational database (using AUTO,
EXPLICIT, etc.) with many elements, attributes, and subelements but now
wonder if there is a "best practice" for designing the structure for going
the other way, XML -> relational. Since I have not yet worked on the data
extraction side, maybe what I've put together makes data extraction awkward
(requiring many lines of T-SQL vs. one or two). But I definitely cannot
stomach the 'all attribute' or 'all element' practices. Between the two
examples below, which is better/easier/more efficient/flexible for
retrieving information (e.g. with OPENXML). I like the first example
theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
ELEMENTS). Thanks for any tips or insights.
<entities>
<entity>
<entityAttribute>nameOfThisEntity</entityAttribute>
<entityValue>valueOfThisEntity</entityValue>
</entity>
<entity>
<entityAttribute>nameOfNextEntity</entityAttribute>
<entityValue>valueOfNextEntity</entityValue>
</entity>
<entity>
...
</entity>
</entities>
vs.
<entities>
<nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
<nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
...
</entities>
Hi Don,
I preferred to the first one, although I do not think there will be much
performance difference between the following two XML structures. The fist
XML structure will be more readable and efficient for search. The following
article will tell you how to optimize SQLXML performance for databases,
including SQL Server 2000.
SQLXML best practice paper on MSDN
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/sqlxml_optimperformance.asp?frame=true
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||If you plan on using OpenXML, then size and ability to query structure
instead of values will most likely make your second format perform better.
Best regards
Michael
"Don Miller" <nospam@.nospam.com> wrote in message
news:es8k9YfLEHA.2396@.TK2MSFTNGP12.phx.gbl...
> I've created a large XML document from a relational database (using AUTO,
> EXPLICIT, etc.) with many elements, attributes, and subelements but now
> wonder if there is a "best practice" for designing the structure for going
> the other way, XML -> relational. Since I have not yet worked on the data
> extraction side, maybe what I've put together makes data extraction
> awkward
> (requiring many lines of T-SQL vs. one or two). But I definitely cannot
> stomach the 'all attribute' or 'all element' practices. Between the two
> examples below, which is better/easier/more efficient/flexible for
> retrieving information (e.g. with OPENXML). I like the first example
> theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
> ELEMENTS). Thanks for any tips or insights.
> <entities>
> <entity>
> <entityAttribute>nameOfThisEntity</entityAttribute>
> <entityValue>valueOfThisEntity</entityValue>
> </entity>
> <entity>
> <entityAttribute>nameOfNextEntity</entityAttribute>
> <entityValue>valueOfNextEntity</entityValue>
> </entity>
> <entity>
> ...
> </entity>
> </entities>
> vs.
> <entities>
> <nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
> <nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
> ...
> </entities>
>

Best book for relational db's?

Greetings,
I'm reentering SQLServer-land after an absense. I've forgotten most of
my school-taught relational/OO database design theory. Normalization is
still in the old memory banks, but it too is fading fast. :)
Can anyone recommend a good book on relational design that can refresh
my skills?
Thanks!See if this helps:
http://www.datamodel.org/DataModelLibrary.html
AMB
"Guerrilla_Coder" wrote:

> Greetings,
> I'm reentering SQLServer-land after an absense. I've forgotten most of
> my school-taught relational/OO database design theory. Normalization is
> still in the old memory banks, but it too is fading fast. :)
> Can anyone recommend a good book on relational design that can refresh
> my skills?
> Thanks!
>

Begginer in SQL-Foreign KEy to Mulitple Tables

Hey everyone,
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy
Hello,

Let's say that we have the following tables:
property (property_id int primary key, property_type int, some general fields)
mountain_property (mproperty_id int primary key, property_id int references property(property_id), some specific fields)
beach_property (bproperty_id int primary key, property_id int references property(property_id), some specific fields)

property_type defines the mountain (1) or beach (2) property.

So when we need to select all records for mountain property we can execute the following SQL:

selecy * from property p inner joun mountain_property mp on p.property_id = mp.property_id where property_type=1.

So genenerally, property table knows nothing about mountain_property and beach_property and here is no relationships from property to any of these tables.

This example is similar to defining a class with collections and this type of relationships is called one-to-many (for each of pair property-mountain_property and property-beach_property).

The other possible (but different) solution is to use one-to-one relationship. In that case mountain_property's primary key is a foreign key to property table. E.g. the structure is:
property (property_id int primary key, property_type int, some general fields)
mountain_property (property_id int primary key references property(property_id), some specific fields)
and
selecy * from property p inner joun mountain_property mp on p.property_id = mp.property_id
will return a list of "mountain_property" objects with all fields.

Sincerely,
Alex|||

There are a couple of examples of what you're after in the Microsoft sample AdventureWorks database.

For example the Store and Individual tables both inherit from the Customer table. Store and Individual both have a primary key of CustomerId, which is a foreign key to the Customer table. Customer has a CustomerType column, which contains either 'S' or 'I' depending on whether the Customer row refers to a Store or an Individual.

This is also the way that my Foundation product implements inheritance; it looks for relationships like this and creates business / data access objects with the same inheritance structure. You can download either the free/trial version (free for up to 20 tables) or just the Knowledge Base from my site, the Knowledge base has a helpful page in it about exactly this stuff, it's called "Defining Inheritance Relationships".

Linq-to-Sql implements inheritance in a different way. You don't have a base table and derived tables. Everything goes into a single table, and any columns that would normally go into a derived table must be made nullable. So you end up with loads of nullable columns, only a subset of which can legitimately be populated for any particular row. Of course having to make the columns nullable means a lack of data-integrity, and you can't have unique indexes that use those columns, and no one-to-one relationships including those columns. Made me shudder when I read about it! Maybe I'm just too sensitive

Linq-to-Sql still uses the "CustomerType column" way of telling what derived type a row is.

Sean

|||Thank your for giving such a detailed example, it really helped. I guess I understand here that there wont be a direct link for the Property table to find one of its children, but that it can find them because it holds the property_type id so it will know what table to search for them in.
They question I have is that in the 1'st SQL statement that you gave it says "selecy * from property p inner joun mountain_property mp" which shows me that this statement already knows that it is seaching in the mountain_property table.
But what would you do if you didn't know which table to search in. As in, lets say you have your property instance (row) in the property table, and you now want to get more information about it(which is either stored in the mountain_property or beach_property table). I think I can see myself doing this in two statements by first getting the property_type and then processing that data in my application to choose what table to choose in and then executing another statement to get the data. Is this the way that you are supposed to do it. Eg below
So I guess if I was in the Property table and I had selected row which was of type mountain_property. To get the additional information of this property (held in the moutain_property table) I would first select the property_type id. I would then use an if/else statement in my application to find out wich table to search for. eg. If(type == 1) table = Mountain; else if(type == 2) table = Beach;. Then I would make another SQL statement that would then search for the data in the table name that I got from the if/else statement.
Is this right, or am I far off from course?

Begginer Confusion

Hey everyone,
I am brand new to SQL and I need some help getting started how to learn about relational databases and how to make the databases via Microsoft SQL(I have visual Studios PRO) and access and use them via C#.
I believe that the program that I am writing will be significantly better with a database. What I am doing is making a program that stores user input. But each user input that a person stores can have a reference to another user input, hence why I think that a relational database to store this information will be work out well. I also just want to learn more about databases because I think that it will be a good step in me learning more about programming.
What I need from you guys is a point into a direction for a book or a specific spot in a website that will introduce and teach me how to design these relational databases via Microsoft SQL. I have programmed alot with Java and recently switched over to C#, using the .net framework and found it to be great. I need to find out how to make these databases but also how to connect and access them through my application that will be coded in C#. I have "Programming in C#" by O'Reilly but it really doesn't go in that much about SQL databases.
The question that I really have come from the limited knowledge that I have gained looking online and talking to my brother (progamming whiz). A database is managed by a DBMS and clients can interact with the DBMS to get to the database. The question to me is how does one interact with a DBMS through a programming language like C#. I guess I am just really confused at the this link. If anyone could point me into the direction of a book to answer these question but help teach me to apply it to my own program, I would really appreciate it. Thank your for time.
P.S.
I have found some books online but I dont really know which one I really need. One that I found is called "Learning SQL on SQL Server 2005". If you have read, I would appreciate your opinion. Thanks

Here are two excellent resources for 'getting your feet wet'.

The ASP.NET site has quite a few videos about other topics -but if you scroll down, you will find some for incorporating databases in the project.

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

http://www.asp.net/learn/videos/default.aspx?tabid=63#sql

The Microsoft Press 'Step by Step' series are good for starting out.

|||Thank you for both of those sources. I will check them out right now. Also, one more question. If I use the SQL database for my application, will all the clients, and everyone that downloads my program have to have SQL installed on their computer so that it will work?
|||

If it is a web based application, the data server is usually located with the web server -all users only require a browser.

If it is a stand-alone Windows based application, you probably would distribute the database (and the server) with your application. (Most likely using the free SQL Server Express.)