Hi!

I am developing a website that lets registered users to have their own websites like user.domain.com.

I have two tables in my mysql db

First table ACCOUNTS that holds id, login and password.
Second table USERDETAILS which holds id, account_id, some personal user details.

What I need is some more tables for user's website content, galleries and css attributes. Those would have to be linked with user's account id.

User can add following sites to his website (he/she picks it from the list of available types):
HOME
GALLERY
CONTACT
CUSTOM

Now two users can have different number of second level sites, for example:

KATE has only HOME and CONTACT
NATALIE can have HOME, GALLERY, CONTACT and PETS (which is CUSTOM site).

There is unlimited number of CUSTOM sites per user.

Furthermore pictures in galleries are grouped in albums.

As there will be some simple template system based on css attributes there have to be some way of storing them.

My problem is How do i design database like this, and how to split all the data into tables?

Any help on this would be a blessing...

Well I doubt anyone here will give you a full solution on a silver plate.

But just one or two hints as a start:
Put your few account and user details into one table (i.e. just "ACCOUNTS" with all info on the user you need)!

Create a table "SUBPAGES" for your subpages (HOME,GALLERY,CONTACT,CUSTOM), just to store what subpages you're actually offering (which can be updated easily this way), and which is being referred to by your" Package" table:

Create a "Package" table for your different hosting packages, that contains the specifications as to what subpages the clients are allowed to have...
containing columns like
PKG_ID
TOTAL_PAGES_ALLOWED
SUBPAGE (with a FK, referring to SUBPAGES table above)

These are just initial suggestions to start off... draw your own DB structure as you imagine it and see what may work and what wouldn't.

Also you may consider storing the pre-defined templates of your subpages in a CLOB-like column of a "TEMPLATES" table or so... don't know about mySQL's ability for that though...

Well... post back once you're further or done so we can see what became of it.

First of all - thank You very much for posting. I am not expecting final solution - just some suggestinos which data goes where (which table) to make it faster..



What I have right now are a few tables:
ACCOUNTS (containg Id and general information which needs to be read when someone enters the user's website - just a few columns)

DETAILS (accountId and detailed user information - for contact purpose etc)

PAGES (which contains accountId and subpage name, visibility and other page-specific columns)

CONTENTS (which contains pageId and content - text+html)

Lets not talk about templates for a moment. I am wondering is above solution going to be fast enough. I am hoping for 1000+ users.

There are no Packages - every registered user can have all the subpages.

Any better solution when it comes to storing subpages and its contents?