SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    330
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Only One Database?

    I've been using HostMySite for several years, for three very simple websites, quite happily.

    But recently I decided I would learn more about writing ASP.NET code to interface with a Microsoft SQL Server so I upgraded one of my sites from their "ASP.NET" hosting package to their "ASP.NET+" package. The difference is that the latter gives me "MS SQL Server 2005 or 2008" with 600MB of storage. My plan is to use this to learn about database concepts, control-binding, ADO, etc. (i.e., just for study, not for commercial use)

    When I went to create a database to start playing with, HostMySite told me I could only have ONE database. It could be 600MB with a jillion tables and relationships but I only get to create ONE!

    Is this common in the web-hosting world? Why do they care how many databases I have as long as I don't exceed the storage or bandwidth limits? How do other hosting companies do it with MS (not My-) SQL?

    Thanks in advance.
    My Art and Photography Website - pnArt.com -
    My Blog - blog.nelsondev.net -

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Why do you need more than one database. More than one database is like more than one universe - if everything imaginable fits in one why should there be more than one.

    Just use different prefixes for the tables for each application and set up different users that have access to the different combinations of tables and your one database will handle everything that a billion databases could handle without needing more than one.

    The whole point of creating a database back when the concept was first though of is to make it a central repository for ALL data. Having more than one database defeats the purpose for which databases exist.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    Boston, MA
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As mentioned technically you can get by with one database as it is all you will ever need.

    Personally I prefer using one database per script I use however I did used to get by with just 1 back in the day and did just fine. One benefit would be that for backups you only have one database to backup instead of multiple.

    You can probably find a host that will give you unlimited databases and tons of diskspace and bandwidth you will never use but if the service stinks well it kind of defeats the purpose
    www.okihost.com
    Quality Hosting Solutions Since 2002

  4. #4
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    330
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    Why do you need more than one database. More than one database is like more than one universe - if everything imaginable fits in one why should there be more than one.
    Because I'm doing this as a learning/educational exercise, the advantages of multiple databases I can see are:

    1. I can experiment with different normalization schemes or run the risk of totally clobbering some database with a runaway set of newbie SQL transactions without wrecking other databases.

    2. I can expose certain database's passwords, etc, to the public so I can request help in various sw developers' forums on particular topics I'm struggling with without exposing my whole site.

    But, yes, you're right that what I'll have to do to cope with this is to create one humongously-complicated database with "some of this and some of that" to use as a learning exercise. It's just that on my desktop PC, where I have MS SQL Server installed, I create a separate database for each new concept I want to learn or experiment with.
    My Art and Photography Website - pnArt.com -
    My Blog - blog.nelsondev.net -

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by plnelson View Post
    1. I can experiment with different normalization schemes or run the risk of totally clobbering some database with a runaway set of newbie SQL transactions without wrecking other databases.
    You can do that within one database. Just set up a username that only has access to a group of tables and that user can't clobber anything else.

    Quote Originally Posted by plnelson View Post
    2. I can expose certain database's passwords, etc, to the public so I can request help in various sw developers' forums on particular topics I'm struggling with without exposing my whole site.
    If the username only has access to a few tables anyone using that username and password can't see any of the other tables and has no way of telling that they are there. No difference between the users and matching tables being in separate databases or all in the same one.

    Internally all the databases on the one server are treated as parts of the one database anyway so whether you set your part of that database up as one or many doesn't make it work any different.

    You might want to read up on the GRANT command which is used to define user privileges such as what tables a given user can read and what ones they can read and write.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    Non-Member MyDomAinZ.us's Avatar
    Join Date
    Feb 2008
    Location
    Fort Bregg, North Carolina, USA
    Posts
    798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To allow only one database will be their hosting policy. You should check the plans before subscribing the services.

    Good luck

  7. #7
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    330
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    You can do that within one database. Just set up a username that only has access to a group of tables and that user can't clobber anything else.
    I didn't know I can do that! (See? Proof that I'm a MSSQL newbie!) This is the GRANT command you mentioned? I looked in my O'Reilly SQL Pocket Guide (2nd Edition) and I don't see GRANT DENY or REVOKE in the index, but they're all over the place on the web - any idea what's up with that? ( Thanks in advance!! )


    Internally all the databases on the one server are treated as parts of the one database anyway so whether you set your part of that database up as one or many doesn't make it work any different.
    Really? So if I'm experimenting with OTLP veresus OLAP, say, or different schemes to optimize for writing as opposed to reading, I won't see any performance difference?
    My Art and Photography Website - pnArt.com -
    My Blog - blog.nelsondev.net -

  8. #8
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    330
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MyDomAinZ.us View Post
    To allow only one database will be their hosting policy. You should check the plans before subscribing the services.

    Good luck
    Easier said than done. When I ran into this yesterday I called them and asked where, exactly, does it say it on their website and they couldn't find anyplace! I then talked to a supervisor at HMS and they couldn't find it either. Even if it's there someplace, if they can't find it I don't know how the PDU is supposed to find it.

    Anyway, my question was whther this is common in the webhosting world and why web hosts would even CARE how many databases you have as long as you don't exceed the storage and bandwidth limits.

    But, thanks to felgall I might have a work-around for at least part of the problem so my question migh be more academic at this point.
    My Art and Photography Website - pnArt.com -
    My Blog - blog.nelsondev.net -

  9. #9
    Non-Member MyDomAinZ.us's Avatar
    Join Date
    Feb 2008
    Location
    Fort Bregg, North Carolina, USA
    Posts
    798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by plnelson View Post
    Anyway, my question was whther this is common in the webhosting world
    Our answer is 'not at all'
    and why web hosts would even CARE how many databases you have as long as you don't exceed the storage and bandwidth limits.
    It depends upon webhost-to-webhost.

    Regards.

  10. #10
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by plnelson View Post
    I didn't know I can do that! (See? Proof that I'm a MSSQL newbie!) This is the GRANT command you mentioned? I looked in my O'Reilly SQL Pocket Guide (2nd Edition) and I don't see GRANT DENY or REVOKE in the index, but they're all over the place on the web - any idea what's up with that? ( Thanks in advance!! )
    Page 64.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  11. #11
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    330
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    Page 64.
    Nope.

    Page 64 is CUBE and ROLLUP. This is 2nd edition brand new from Barnes and Noble 2 weeks ago. Maybe there's a Third Edition out?

    But That's OK - as I said, it's all over the web now that I know to look for it - it just makes me wonder what else is missing from the Pocket Guide that I might want to know.

    BTW, another surprise from HostMySite - when I hooked up to my new database from my home PC using Microsoft SQL Server Management Studio Express I could see a listing in the Object Explorer of all the other databases on that server. I tried expanding a few to see if I could because that might tell me how exposed MINE was and most of them were protected but ONE that I tried wasn't!

    Isn't SQL server configurable to prevent this? I'm new to SQL and databases, but so far this is very interesting.
    My Art and Photography Website - pnArt.com -
    My Blog - blog.nelsondev.net -

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by plnelson View Post
    it just makes me wonder what else is missing from the Pocket Guide that I might want to know.
    most likely, just more DBA-oriented stuff

    i've got the first edition, and the Intro clearly states that it is about SQL, the language used by all database systems, and that the book specifically covers SQL that works in Oracle, DB2, SQL Server, and MySQL

    database administration commands would typically be covered only in a book oriented to a specific database system

    Quote Originally Posted by plnelson View Post
    BTW, another surprise from HostMySite
    your host is negligent
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Certified Ethical Hacker silver trophybronze trophy dklynn's Avatar
    Join Date
    Feb 2002
    Location
    Auckland
    Posts
    14,692
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)
    http://dev.mysql.com/doc/refman/5.0/en/grant.html

    Always go to the front end of the horse for good information!

    Regards,

    DK
    David K. Lynn - Data Koncepts is a long-time WebHostingBuzz (US/UK)
    Client and (unpaid) WHB Ambassador
    mod_rewrite Tutorial Article (setup, config, test & write
    mod_rewrite regex w/sample code) and Code Generator

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    david, you're talking to the wrong horse altogether

    plnelson is using MSSQL (MicroSoft SQL Server)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    david, you're talking to the wrong horse altogether

    plnelson is using MSSQL (MicroSoft SQL Server)

    Cross browser css bugs

    Dan Schulz you will be missed

  16. #16
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Why do you need a host for this at all? You can get SQL Express free and databind at home, with full control of the system, to your heart's content. Especially with SQL server where having root access makes alot of stuff easier.

  17. #17
    SitePoint Zealot MikeDVB's Avatar
    Join Date
    Mar 2008
    Location
    Indiana, USA
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    Why do you need a host for this at all? You can get SQL Express free and databind at home, with full control of the system, to your heart's content. Especially with SQL server where having root access makes alot of stuff easier.
    From a learning standpoint, this is exactly what I was wondering myself. Set up a development environment on your home PC and learn there... Cheaper, faster, easier.
    Michael Denney - MDDHosting, LLC - Hosting over 8,500 domains!
    http://www.mddhosting.com/ - Celebrating 3 years in business!
    Serious Internet Solutions, LLC. - Quality Unmanaged VPS
    SeriousVPS - http://www.seriousvps.com

  18. #18
    Certified Ethical Hacker silver trophybronze trophy dklynn's Avatar
    Join Date
    Feb 2002
    Location
    Auckland
    Posts
    14,692
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)
    Rudy,

    Quote Originally Posted by r937 View Post
    david, you're talking to the wrong horse altogether

    plnelson is using MSSQL (MicroSoft SQL Server)

    BUMMER! How'd I miss that one?

    Does M$ have a similar documentation system for MSSQL?

    Thanks for catching that!

    Regards,

    DK
    David K. Lynn - Data Koncepts is a long-time WebHostingBuzz (US/UK)
    Client and (unpaid) WHB Ambassador
    mod_rewrite Tutorial Article (setup, config, test & write
    mod_rewrite regex w/sample code) and Code Generator

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dklynn View Post
    Does M$ have a similar documentation system for MSSQL?
    yes

    it is affectionately known as "BOL" == Books OnLine

    i have downloaded it as a handy-dandy .chm file, but you can also access it over teh interwebs

    it is available here -- http://msdn.microsoft.com/en-us/library/bb510741.aspx

    you should see two window panes

    the right side is the content, the left side is the navigation

    in the left pane, scroll down and click on "WITH common_table_expression (Transact-SQL)"

    you should end up at this url -- http://msdn.microsoft.com/en-us/library/ms175972.aspx

    if these instructions worked, then congrats, you are now a BOL user
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •