[BIZ] Database designers.. what's the best way to.....

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Tempest
    Too lazy to set a custom title
    • May 2004
    • 10217
    #1

    [BIZ] Database designers.. what's the best way to.....

    Set up multiple databases and scripts to use some common data. For example, let's say I have a bunch of different scripts (that should/could be standalone), but they all need some of the same data.. plus they can all add to that data... eg: let's say I'm storing a bunch of domains.. Multiple scripts (Traffic trading, TGP, Thumb etc.) on multiple sites need to read and write to "a" list of domains. Ideally I want it to be the same list so I have one central comprehensive listing.

    Should that data be in it's own DB and all the scripts take the extra connection hit to access it and write to it and also lose the ability to perform queries by joining that data with other data in the script specific DB?

    Should each script have it's own copy of the data and when a particular script adds a new piece of data it does it to all of the other DBs at the same time?

    Is there a better option? Any ways to make these options more efficient?

    I'm using MySQL.
    Tags: None
    • dozey
      Confirmed User
      • Nov 2004
      • 552
      #2
      Could use replication, though it may not always be up to date.. and I imagine you will lose some transaction locking ability.

        Comment

        • Varius
          Confirmed User
          • Jun 2004
          • 6890
          #3
          Will it be on the same server? If so, you can have different databases and access them without opening new connections. Cross-database joins also work fine in mysql.

          Example:

          you create three databases: common, site1, site2

          You can do queries such as:

          SELECT a.field1, a.field2, b.field2 FROM site1.table1 a JOIN common.table1 b ON a.field1=b.field1;

          Basically, in your queries that use more than one database just have the dbname in the query before the table name.

          Hope that answers your question.
          Skype variuscr - Email varius AT gmail

            Comment

            • Sansa
              Confirmed User
              • Apr 2007
              • 293
              #4
              I see nothing wrong with using one database and have scripts connect to it. Make sure your scripts use persistent connections and if you're doing a lot of updates make sure to use InnoDB instead of INNODB table type. Also make sure to set up your primary keys and indeces. If you're doing lots of updates the table should have a numeric primary key (auto_increment) and do updates based on the primary key.
              Beat the shitty economy by becoming a loan shark.

                Comment

                • Tempest
                  Too lazy to set a custom title
                  • May 2004
                  • 10217
                  #5
                  Originally posted by Varius
                  Will it be on the same server? If so, you can have different databases and access them without opening new connections. Cross-database joins also work fine in mysql.

                  Example:

                  you create three databases: common, site1, site2

                  You can do queries such as:

                  SELECT a.field1, a.field2, b.field2 FROM site1.table1 a JOIN common.table1 b ON a.field1=b.field1;

                  Basically, in your queries that use more than one database just have the dbname in the query before the table name.

                  Hope that answers your question.
                  I've never tried cross database joins.. I'll have to give that a go as that would work very nicely. How do you handle the DB connections in this case? Which linkid would you use?

                    Comment

                    • Tempest
                      Too lazy to set a custom title
                      • May 2004
                      • 10217
                      #6
                      Originally posted by Sansa
                      ...make sure to use InnoDB instead of INNODB table type...
                      What are the benifits/drawbacks to InnoDb instead of INNODB?

                      I pretty much always use auto_increment primary key Ids in my tables.

                        Comment

                        • Varius
                          Confirmed User
                          • Jun 2004
                          • 6890
                          #7
                          Originally posted by Tempest
                          I've never tried cross database joins.. I'll have to give that a go as that would work very nicely. How do you handle the DB connections in this case? Which linkid would you use?
                          Open your connection to any of the database names, doesn't matter. Or none at all even like this with native php mysql functions:

                          $sock = mysql_connect('host','user','pass');
                          $sql = "whatever";
                          $res = mysql_query($sql,$sock);

                          In that case though you must make sure your query specifies the db name, otherwise you'd get an error.
                          Skype variuscr - Email varius AT gmail

                            Comment

                            • Varius
                              Confirmed User
                              • Jun 2004
                              • 6890
                              #8
                              Originally posted by Tempest
                              What are the benifits/drawbacks to InnoDb instead of INNODB?

                              I pretty much always use auto_increment primary key Ids in my tables.
                              For rows that change often or for big tables use InnoDB. For reference tables, that say have few rows that never change, choose INNODB.

                              Best you check mysql.com for full comparisons between all engine types though.
                              Skype variuscr - Email varius AT gmail

                                Comment

                                • chuvii
                                  Confirmed User
                                  • Sep 2006
                                  • 691
                                  #9
                                  create more databases
                                  or tables
                                  Mainstream and Adult Design

                                    Comment

                                    Previous template Next
                                    Working...