Querying a database with mulitple tables

For my major project i'm going to create a database driven website and the relational database design need to be normalised to 3rd normal form. I can see myself having over 20 database tables.
I can also see that in the web application i'm going to need to return data from more than one table.
To do this i can use join's and join on the primary key's. However this means my query string are going to be huge!

Is there a better method of doing this?

Comments

  • : For my major project i'm going to create a database driven website and the relational database design need to be normalised to 3rd normal form. I can see myself having over 20 database tables.
    : I can also see that in the web application i'm going to need to return data from more than one table.
    : To do this i can use join's and join on the primary key's. However this means my query string are going to be huge!
    :
    : Is there a better method of doing this?

    Query strings aren't a very good idea anyways for a number of reasons. One good alternative is to encapsulate all of the SQL in stored procedures, assuming your database engine supports them. It depends a lot on what language you're coding the application in, the libraries it supports, and the database you're using for the storage.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

  • [b][red]This message was edited by Ribbleton at 2006-1-12 6:49:28[/red][/b][hr]
    : : For my major project i'm going to create a database driven website and the relational database design need to be normalised to 3rd normal form. I can see myself having over 20 database tables.
    : : I can also see that in the web application i'm going to need to return data from more than one table.
    : : To do this i can use join's and join on the primary key's. However this means my query string are going to be huge!
    : :
    : : Is there a better method of doing this?
    :
    : Query strings aren't a very good idea anyways for a number of reasons. One good alternative is to encapsulate all of the SQL in stored procedures, assuming your database engine supports them. It depends a lot on what language you're coding the application in, the libraries it supports, and the database you're using for the storage.

    Thanks for the reply.

    Think i'm going to use a MSDE Database so should be alright with the stored procedures. I think also using Stored procedure will make your code more tidy - I find i have Sql strings all over the place.

    My only problem is i'm going to have 50+ stored procedures.
    I was talking to someone and they said why not create a DLL with all your sql string in it and then referenace than.

    Still think the Stored proc is the best answer?


  • : Think i'm going to use a MSDE Database so should be alright with the stored procedures. I think also using Stored procedure will make your code more tidy - I find i have Sql strings all over the place.
    :
    : My only problem is i'm going to have 50+ stored procedures.
    : I was talking to someone and they said why not create a DLL with all your sql string in it and then referenace than.
    :
    : Still think the Stored proc is the best answer?

    Generally speaking, yes. I'm not familiar with MSDE, I use Oracle, but the principles are probably the same. When you write SQL in a stored procedure, that procedure (and the SQL in it) get compiled and stored in the database. When you pass a SQL string at runtime, the database has to parse/compile it as part of the execution. So, in theory, you're saving a bit of processing on each call to the database.

    Also, read up on "sql injection" for another reason why stored procedures (or at minumum, parameterized queries) are better than constructing SQL strings at runtime. The system I work on has tens of thousands of stored procedures. Oracle lets you group them together into "packages", which are kind of like modules in most programming languages. If your database supports that then I recommend using it.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

  • : : Think i'm going to use a MSDE Database so should be alright with the stored procedures. I think also using Stored procedure will make your code more tidy - I find i have Sql strings all over the place.
    : :
    : : My only problem is i'm going to have 50+ stored procedures.
    : : I was talking to someone and they said why not create a DLL with all your sql string in it and then referenace than.
    : :
    : : Still think the Stored proc is the best answer?
    :
    : Generally speaking, yes. I'm not familiar with MSDE, I use Oracle, but the principles are probably the same. When you write SQL in a stored procedure, that procedure (and the SQL in it) get compiled and stored in the database. When you pass a SQL string at runtime, the database has to parse/compile it as part of the execution. So, in theory, you're saving a bit of processing on each call to the database.
    :
    : Also, read up on "sql injection" for another reason why stored procedures (or at minumum, parameterized queries) are better than constructing SQL strings at runtime. The system I work on has tens of thousands of stored procedures. Oracle lets you group them together into "packages", which are kind of like modules in most programming languages. If your database supports that then I recommend using it.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    : [code]
    : $ select * from users where clue > 0
    : no rows returned
    : [/code]
    :
    :

    Thanks! you have been a great help.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories