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 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...

There's an easy way and a hard way, and which one you choose depends as much on whether you plan to add any more features any time soon as anything else.

Create Table UserAllowedFeatures (
UserID int foreign key references User (UserID),
Home bit not null,
Gallery bit not null,
Contact bit not null,
Custom bit not null
)

This will let you set a true or false (1 or 0) value telling you whether a user can have the attributes. It's better, though, to have a lookup table like

Create Table Features (
FeatureID int identity primary key,
FeatureName nvarchar(100)
)

And then a permission table, with a UserID, FeatureID, and a boolean do they have permission column. When you change the list of what's available, it will be far easier.

The rest of your question, I'm not really sure how to answer. I get that galleries are grouped into albums and that users can have unlimited numbers of custom web sites, but, I don't know what information you want to store about all of this?

Thanks for the answer.

I think I get what You are saying with these 2 tables and one linking both of them, but I dont know is it necessary to have them since all of those second-level sites will be available to everyone.

What I need is a table(s) structure for storing contents of the second-level sites. Content will be text+html, so I am thinking of some long text variables. I already know how to store galleries:

create table(
album_id INT,
pic_url VARCHAR
)

create table(
account_id INT,
album_id INT
)

, but dont know how to design websites' contents table.

I have to store account_id, name of the second-level site (which is predefinied in case of HOME and CONTACT) + names of CUSTOM sites + all of those sites' contents. It has to be done in such a way that there can be unlimited CUSTOM sites.

EDIT:

Is it really that simple??
create table(
id INT,
account_id INT (linking with user account number),
name_of_the_site VARCHAR,
content TEXT
) PRIMARY KEY(id);

Hope You can show me a better (optimal and fast) solution.

Originally Posted by malina

What I need is a table(s) structure for storing contents of the second-level sites. Content will be text+html, so I am thinking of some long text variables. I already know how to store galleries:

Hopefully somebody who knows MySQL better can come along to shed some light on this. I'm a SQL Server programmer, and generally the theory carries over pretty well, but sometimes implementation details can actually change behavior.

That said, I think you would be much better off storing the actual content in text files, and using the database to store the location of those files. Databases are very good at managing structured data, but variable length unstructured text can easily bring an RDBMS to its knees. Because the text can be huge, and is of (wildly) varying length, it's stored out of row, which is very difficult for the db. There's all kinds of disc and memory overhead. You'll flood your cache (data buffer) bringing the data in and out all the time, hurting the performance of other things running on the same server.

Not to mention that your code will actually get to the data much faster by reading a file, than by waiting for a query to find the right record and then return it.

You may want to read a bit about Schemas before you go too far down your design path. Though you may not need it now - it could allow greater flexibility down the road as you build out your solution. I'm biased to PostgreSQL:
http://www.postgresql.org/docs/8.3/i...l-schemas.html