database size for 100+ mysql database

[andydeans]

hi,

fairly new to mysql.

i have been working on a site which would have say 100 + agents.

basically the agents clients are their own so i thought the best way would be to get a dedicated server and create a mysql database for each agents company.

the database would be the same for each, well slight differences to 1 or 2 tables but the basics the same.

i had thought i would have say "companyname.domain.com" then "companyname1.domain.com"

first of i have 2 questions really.

1. is this ok to have setup on a dedicated server? having that amount of sub domains and with their own mysql database?

2. what is the restrictions to a mysql database? the hosting company said it is down to hardware on the server?
so say it was a dual core linux machine with 4gb ram and 160gb sata hard drive, what would be the size limit for each database if there is one?

hope someone can help me as i have the idea just not sure if it is best practice to do it this way.

many thanks

[asaukani]

hi,

fairly new to mysql.

i have been working on a site which would have say 100 + agents.

basically the agents clients are their own so i thought the best way would be to get a dedicated server and create a mysql database for each agents company.

the database would be the same for each, well slight differences to 1 or 2 tables but the basics the same.

i had thought i would have say "companyname.domain.com" then "companyname1.domain.com"

first of i have 2 questions really.

1. is this ok to have setup on a dedicated server? having that amount of sub domains and with their own mysql database?

2. what is the restrictions to a mysql database? the hosting company said it is down to hardware on the server? so say it was a dual core linux machine with 4gb ram and 160gb sata hard drive, what would be the size limit for each database if there is one?

hope someone can help me as i have the idea just not sure if it is best practice to do it this way.

many thanks

Frankly i don't much have experience in MySQL database but from database architecture point of view i suggest like this:
1. use only one database for all user it is much easier to maintain the data integrity
2. Determine common table and ensure that each site has the same description of the table, resolved all discrepancies
3. use agent id to determine the record for each agents
4. only the headquarter may change common table the agents if needed use extra table
5. standardize the name of extra table to contain code of agents
6. agents can only propose the change on common table
using this way u will save a lot of space, time and cost to maintain control of data and the whole application
for the size of the server i think it is not adequate to host this type of domain
u can also restrict access the database such that each agents can only access their data and common reference table

[andydeans]

thanks for your help i appreciate it.

one more question if that is ok?

the design of my database. should i create a table for each product. they are financial services products so very similar columns but some may have more than the others.

should it be one table for easy querying or best to use seperate tables for each?

many thanks

[asaukani]

thanks for your help i appreciate it.

one more question if that is ok?

the design of my database. should i create a table for each product. they are financial services products so very similar columns but some may have more than the others.

should it be one table for easy querying or best to use seperate tables for each?

many thanks

sorry for quite long time to answer
i proposed u adapt the concept from retail application what we call PLU (price lookup) and SKU (Stock keeping unit)
the concept is like this:
one product can have several variants with common properties like price, name and so on but there are some properties like color, size is different and u want to keep the stock based on the variants
what we do is to create the main table to keep the common properties and for each variants we put one table and linked it to main table
u can use this concept to design one table to keep the common properties and for each product which is different u can derived new table based on main table
if u need u can send direct email to me at [email][e-mail][/email]

i hope it can help u

u build a table which contains the common field

[andydeans]

thanks so much i might mail you direct if that is ok?

thanks again for the help.