November 22nd, 2013, 11:36 AM
Planning stage for mysql
I am fairly new at creating a website but can handle myself with a database. I am in the planning stage of developing a website and trying to figure out my requirements:
I will operate a website where users can log-in and use the site either as a landlord or as a tenant with relationships between the two.
my questions is this:
I know how to setup my database when only one landlord with multiple properties are involved with tenants. What I am not sure about is how to store data online with the database(s) when various landlords that are not related to each other sign up to administrate their tenants. would I need to create a new database for each client? or can they all use the same database but with different [id] and privileges?
Right now I am doing a project for one company but while thinking about it I figured why not offer the same online application to everyone who wants to use it. The thing I need to figure out is my space requirement and bandwith and how to setup a domain with the right host and not knowing how many database I would end up with at the end is where I am stuck.
November 22nd, 2013, 12:02 PM
Well, it is not easy to get your idea, or your description of business-processes. Perhaps you'd better try to compose your thoughts and write more proper specification.
However, the short answer to your doubts is - no.
You need only one database and only one user credentials for any number of clients of your website.
Your web-site manages end-user names, passwords and roles, but when web-site itself (for example PHP script) talks to database, it uses a single database account.
I could not explain you how you should do it exactly since I do not know your business-process. However I can give you an example.
See, you have LANDLORDS and TENANTS. Put them into two different tables
LANDLORD will have fields like NAME and ID (some unique number).
TENANT will have fields like NAME, ID and LANDLORD_ID.
I.e. each tenant stores the number of the landlord to which he is assigned.
(as a matter of fact they could be stored in a single table, call it USERS - where landlords could be distinguished either by dedicated field USER_TYPE or by the NULL in the field LANDLORD_ID)
I think you need to read some basic literature on databases and see examples to get this idea better.
- programming problems for novice coders
November 22nd, 2013, 12:19 PM
my apologies for not being more descriptive. English is not my first language. let me try this again...
I am building a CMMS (computer maintenance management system) with various functions that have already been established on a desktop application. My idea is to push this system out on the web. at the moment I only operate one company in one database. My idea is to offer the system to everyone who wants to use it. The system is quite complex in nature: I have 63 tables dozens of queries, forms and reports. The application deals with maintenance requests, asset condition, contact information for tenants, suppliers, properties.... an accounting module to deal with rent payment, communication and journal logs and the list goes on..... I can visualize using one database for everyone and I would know how to set it up but after a while this database will be HUGE! if I get lets say 100 landlords using my system and potentially thousands of tenants, I can see getting a problem after a while with everything.
Typically speaking, how would a site like mine be administered in regard to the setup of the database to administer all this data? one database per client would make sense to me in order to keep information confidential and safe and secure and also for backup purposes.
this is where I fall short... how many database can I have on my website? will I get problem with a host for making too many databases?
November 22nd, 2013, 12:20 PM
I think this really depends on how big a scope you're talking about. That will dictate just how normalized your database will be.
For example, you mentioned that there may be multiple landlords. Well, can one tenant be a tenant to multiple landlords at once? If so, do you still consider that person the same tenant, just appearing twice? Is it possible for a landlord to be simultaneously a landlord and a tenant?
The best thing to do is really think out the real-world logic of possible scenarios--that will determine how you structure your data.
November 22nd, 2013, 12:25 PM
i used the wrong response button, please see my response below
November 22nd, 2013, 01:02 PM
Originally Posted by aysiu
Ok.... yes, all my table are normalized but this is not what my question is. I have no problem establishing my table and relationships. What I am asking is I need to keep data that is particular to a company separated from other companies who wants to use my system. these companies are totally unrelated to each other. one might be in Toronto Canada and the other might be in British Columbia Canada. They have nothing in common with each other. the only thing they do have in common is they use my system to administer their properties.
What I am trying to learn is this: if I was to continue the way I am doing on desktop I would simply copy the schema of my database and create another database for another company to use. When I talk about having my system online I have one website, the portal through which all these different companies will come through to use my cmms. what I want to know is how many database can I create on one site? is there a restriction as to how many I can create? is there restriction to the size of a database? Some company can be very large with dozens of properties and thousands if not hundreds of thousands of tenants. I need to know how to plan for expansion.
November 22nd, 2013, 01:41 PM
Well, I see I perhaps misunderstood your concerns - be sure, English is not my native language too (but God bless it for we can at least communicate )
I would say it does not sound like "huge". I myself am working now in my company on a project which have tens thousands of users (and there are about hundred tables, several million transactions) and when we talk of it we all agree that is very small database. We use MySQL and it is sufficient.
I remember that about 3 years ago I worked in other company where millions of transactions were created each months. Well, it was medium database and we used Oracle. However, it was not still big. Companies which provide cell phone nets in large cities have millions of transactions daily (or hourly). That is what should be considered big. Greater than this are storages of search engines, largest social networks etc - they usually could not rely on storing data in a single relational database and invent their own ways.
Now returning to our case. It still do not look like your database should give problems as long as the relationships are well-designed.
Even MySQL can easily enough handle millions of records in a single table (if they are used and indexed properly). Database of ten gigabytes is not a rare matter. 1.5Gb databases are provided even by free hostings. Payed hostings usually have much higher limits. And of course you can at some point switch to VPS with any reasonable resources.
MySQL for example have no limits on number of databases.
However, I should warn you: you should not create separate databases for each of companies only because they are different companies.
If the companies are processed in the same way - and their data structures are similar - put them all together. There is no any sane reason to separate them. Creating multiple databases will only give you headache of support - the more headache with growth of your business. However, here are no technical problem or limit at all.
As I stated above - it is the matter of web-site software to separate the data logically, but physically they preferably should be united.
- programming problems for novice coders
November 22nd, 2013, 02:15 PM
I was fearing having to create many databases. So if mysql can handle the load then one database should do nicely. if it grows out of proportion then I can always split the database later on.
Ok this answers my question. thanks a bunch. I more than likely will come back later on with design questions I am sure.
Last edited by Guelphdad; November 25th, 2013 at 12:29 PM.
Reason: fixed quote tag