actually u only need three tables

  1. main category with key main-category-key and other fields
  2. sub category with primary key : sub-category-key with foreign key main-category-key and other fields
  3. item with primary key is item-key with foreign key sub-category key, and other fields
    alternatively because ur data is small for number 3 u can use another foreign key main-category-key such that u don't need to go through sub category to find the main category your item is in
    i hope it is useful

why don't u go to university's library and find out what previous students do in their thesis
U can compare several thesis to give u an idea
Usually Table of Contents is similar to outlines

[QUOTE=andydeans;1188337]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[/QUOTE]

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][/email]

i hope it can help u

u build a table which contains the common field


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 "" then ""

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[/QUOTE]
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 ...