Thanks everyone for the advice! It was really helpful and Happy New Year! On Mon, Dec 24, 2012 at 5:14 AM, Martin Gainty <mgai...@hotmail.com> wrote:
> To add to Admiral Nelsons observations > > As your database grows your options are to either scale up (boost Memory > and Disks) or scale out (add more MySQL nodes) > If you want to add MySQL additional nodes (without incurring > resultant) downtime you may wish to consider > MySQL Clustering > > > http://books.google.com/books?id=_3nERYD9xqcC&pg=PA2&lpg=PA2&dq=MYSQL+and+%22Reasons+to+Cluster%22&source=bl&ots=L_i0E9iBZe&sig=ZUqEGux6YLa7lrtfQ_rU5pIOd14&hl=en&sa=X&ei=IVPYUJXbAbDJ0AHiq4H4BA&ved=0CDIQ6AEwAA#v=onepage&q=MYSQL%20and%20%22Reasons%20to%20Cluster%22&f=false > > Boxing Day is December 26th > Martin Gainty > ______________________________________________ > > Please do not alter or otherwise disrupt this transmission. > > > > > Date: Sun, 23 Dec 2012 22:39:01 -0800 > > Subject: Re: Animation studio asset management > > From: chadver...@gmail.com > > To: mich...@j3ksolutions.com > > CC: mysql@lists.mysql.com > > > > > Thank you for the responses. > > > > The database would only be accessed internally by about 30-60 users max. > > An average show could add anywhere from 5,000-20,000 records over a > period > > of a few months. We would do maybe 3-4 shows a year. > > Maybe a few dozen records would be inserted daily, updates would not be > as > > common. > > About 20-30 users would occasionally be running queries simultaneously, > > worst case would be every employee (maybe 50-75) which would be highly > > unlikely. > > We currently have no dedicated database developers or design experts, > > although if we grew to 50-75 we would probably consider hiring one (or > two). > > There are not really any security requirements as the server is local to > > our network and won't contain sensitive information. > > > > My gut feeling is to go with the simplest solution and use one database, > > however since I'm not a database design expert, I want to make sure I'm > not > > missing anything important. If and when we get over 100k records in a > > single table, would we need to worry about performance then? I'm not > > really familiar with what the scale of a large database is. If Dan's > > statement of millions of rows is correct, then I probably don't need to > > consider size as one of our performance considerations as it would take > us > > quite a long time to get that many entries. Also, many of these records > > would be related to older shows and wouldn't be accessed as much. > > > > Thanks again for your expertise. > > > > Chad > > > > > > On Sun, Dec 23, 2012 at 9:14 PM, Michael Anderson > > <mich...@j3ksolutions.com>wrote: > > > > > Database performance (like system performance) is always driven by a > give > > > and take process, a trade-off between resources (this includes design > > > expertise) available, and the required results. > > > > > > How many records are you dealing with? > > > How often are records are being added and/or updated? > > > How many users will be running queries simultaneously, number of > readonly, > > > and number of updates and inserts? > > > > > > If only a handful of users, and less than 100,000 records, that rarely > > > change, then you'll probably never have performance problems. > > > > > > However, it you have 100,000 users running queries simultaneously, and > > > changing records regularly, then you may have some issues to address. > > > Their are literally hundreds of solutions to performance problems, and > the > > > right solutions will depend on your particular requirements. > > > > > > Security, is much the same, dependent on the security requirements. > > > Example: > > > Security auditors claimed that the marketing product database was not > > > secure. > > > They said that it needed to be more secure. > > > The CEO of the company asked a simple question, he said: > > > "Whats the worst that could happen if an outsider gains access to this > > > database?" > > > Answer: "They might buy one of your products!" > > > Lesson learned: Don't waste your time securing data that doesn't need > to > > > be secured. > > > > > > So, what are your requirements? > > > > > > > > > > > > > > > > > > On 12/23/2012 09:11 PM, Chad Vernon wrote: > > > > > >> Hi there, > > >> I'm designing an animation studio database to track assets across > multiple > > >> shows. My original design was to have a "show" table to track which > asset > > >> belongs to which show with a show_id column. A couple coworkers > suggested > > >> having a database per show because they are worried about performance > and > > >> reliability vs a single database as the number of shows grows. I don't > > >> think reliability would be different because it's all on the same > MySQL > > >> server anyways. Is that assumption correct? I've read a few articles > > >> about multi-tenant database design, however the motivation for > multiple > > >> databases seems to be security which doesn't apply to us. Am I > correct in > > >> trying to push a single database design for this? If it helps, we > have no > > >> dedicated dba and currently a single mysql server machine. > > >> > > >> Thanks, > > >> Chad > > >> > > >> > > > >
