Table Design- Mysql

Question

Let me try to explain my situation for the application what I am working on,

I have close to 7 categories, lets say A,B,C,D,E,F and G.These categories dont have any table in DB side.Now User selects some categories out of these 7 , lets say C,so in category C, I have another bunch of subcategories, namely C1,C2,C3 to C30.Lets assume now that users Selects C2, for C2 subcategories I have table in the database which gets inserted once user enters all the data via front end.Now C2 table has got 8 columns namely c2t1id,c2t2....c2t8. It has autoincrement id column as well which gets autoincrements for every insert to that table and its primary key for that table.

Now my problem, is that I need to show the users all inserts they have across the application in all the tables.User can do insert in all 7 categories and each category has subcategory upt0 30 and as per existing design this becomes close to 210 tables.

Now my login table is structured with id/name/pw.

Now looking at my design from the prospective of db side, I am starting to get the sense that this design is somewhat flawed and I am starting to get the sense that just to retreive 5 records of the user, I need to search 200+ table with long mulititable select join ?

I need some hints as how I should be proceeding further on this.I am ready to redesign in case if it required.

Answer 1

It definitely sounds like you need to examine your design before proceeding further.

Rather than talking in generalities, talk in specifics. It's hard for people to recommend a database design when they don't know the details of the data.

So, tell us specifically what the data is.

EDIT:

Given what you've told us about your data, it'd look something like this:

USER
----------------------
ID (PK) INTEGER AUTOINCREMENT NOT NULL
EMAIL (PK) VARCHAR(1000) NOT NULL
FIRST_NAME VARCHAR(50) NOT NULL
LAST_NAME  VARCHAR(50) NOT NULL
ENCRYPTED_PASSWORD ....etc


POST
--------------
ID (PK) INTEGER AUTOINCREMENT NOT NULL
USER (FK) INTEGER NOT NULL
CONTENT TEXT NOT NULL
DATE_POSTED TIMESTAMP NOT NULL
DATE_EDITED DATETIME NOT NULL


POST_CATEGORIES
--------------------
ID (PK) INTEGER AUTOINCREMENT NOT NULL
CATEGORY_ID (FK) INTEGER NOT NULL
POST_ID (FK) INTEGER NOT NULL

CATEGORY (hierarchal table)
------------------------------
ID (PK) INTEGER AUTOINCREMENT NOT NULL
PARENT_ID INTEGER
CATEGORY_NAME VARCHAR(200) NOT NULL

Note that the CATEGORY table is using a simple agency list model. Other types of models are possible. See the excellent Managing Hierarchal Data in MySQL article for more details.

With this design, getting a list of user posts would be a matter of:

select * from POSTS where USER = (select ID from USER where FIRST_NAME = 'John' and LAST_NAME = 'Doe');

If you want to add on the categories, you'd join the POST_CATEGORIES and CATEGORY tables too. Obviously this is a simplified example, but hopefully you can see how this design would support hundreds or thousands of categories with hundreds of thousands or millions of posts in just a handful of tables.

Answer 2

Yup, that design is a loser. 210 tables? Think again. Stop thinking in terms of tables and columns and express it as the problem you're trying to solve. Maybe a relational database isn't appropriate here.

Answer 3

A hierarchical structure with a table per node is not the usual answer - I'd suggest checking out the answers to this question to see if any of the advice applies to your problem.

Answer 4

Based on your comments/explanation to @wadesworld Here is a simple design that might point you in the right direction

I have kept the structure as simple as possible, you can all more columns/details to suit your application, and I have shown a few samples of how you can query your data.

TABLE: USERS
ID | USERNAME | PASSWORD

TABLE: CATEGORIES
ID | CATEGORY_ID | NAME

TABLE: POSTS
ID | CATEGORY_ID | DESCRIPTION | USER_ID



// list all top level categories (We use NULL in categories.category_id to indicate it has no parents)
SELECT * FROM categories WHERE category_id IS NULL;

// list all sub categories for category_id = 2
SELECT * FROM categories WHERE category_id=2;

// list parent and child categories, for use in a html select
SELECT parent.id, parent.name, child.id, child.name
  FROM categories AS parent
  JOIN categories AS child ON child.category_id=parent.id
  WHERE parent.category_id IS NULL

// insert a post into category_id=2 for user_id = 1
INSERT INTO posts (category_id, description, user_id)
  VALUES ('2', 'test post', 1);

// select all posts by user with username=tom
SELECT * FROM users
 JOIN posts ON posts.user_id=users.id
 JOIN categories ON categories.id=posts.category_id
 WHERE users.username='tom';

 // count the number of posts made by tom in each category
 SELECT categories.name, COUNT(*)
   FROM users
   JOIN posts ON posts.user_id=users.id
   JOIN categories ON categories.id=posts.category_id
   WHERE users.username='tom';
   GROUP BY categories.name