Pre Building DB thoughts

Forum Moderators: open

Message Too Old, No Replies

Pre Building DB thoughts

Weighting options

henry0

8:54 pm on Dec 11, 2006 (gmt 0)

I am working on a project that will be MySQL based (and PHP)
Two options come to my mind
A) A large table with about 15 cols (mostly varchar from 10 to 250) out of the 15 cols 3 are rating dedicated (total, average etc..)
And a category col
Since 10 categories will exits 10 such tables will be required
Or
B) The same cols plus 10 cols for all the categories
Therefore using only one table.

Here is the question:
Knowing that a few thousands rows will be added and keep growing....
and that also search will be performed as well as ranking calculations

From a speed and organization stand point of view which model should I go with?
A or B?

thanks

physics

9:05 pm on Dec 11, 2006 (gmt 0)

A. Otherwise you have to store a lot of redundant data (categories) and also if you want to change a category name you cannot just do it in one simple place if you choose B. In fact you should look at your table design and find any other fields that might be better off split into another table ... i.e. normalize as much as is (reasonably) possible.

henry0

9:14 pm on Dec 11, 2006 (gmt 0)

Got it,
Thank you.
Henry

jtara

10:34 pm on Dec 11, 2006 (gmt 0)

Since 10 categories will exits 10 such tables will be required

Why?

Can you explain what you mean by categories?

If you are talking about the records in the main table having membership in multiple categories, then your choices are:

1. 10 boolean attributes in the main table indicating membership or non-membership in the category

2. two additional tables (not 10). One with codes and descriptions of categories, and one relating widgets to categories.

I would never do (1). It's too inflexible. What if you want to add an 11'th category?

I'm not sure what you were intending to do with 10 tables.

Oh... you want to have 1 table for each category, listing the widget IDs of the widgets in that category?

If so, I think you are better off with two additional tables, then you can handle any number of categories.

widget
id
name
frumpleness
description

category
id
description

widget-category
widget id
category id

henry0

11:06 pm on Dec 11, 2006 (gmt 0)

Well, it's more like a directory (even if not!)
ID, password of the "poster", item category, item description, item location (geo location: full address, State and County), item comments, item ranking, timestamp, activated(Y,N), user_level.

there are 10 different categories with exactly the same DB criteria.

search will be by Category, by State, by County

physics

11:56 pm on Dec 11, 2006 (gmt 0)

I'd suggest something like jtara said, so maybe
ID, password of the "poster", category_id, item description, item location (geo location: full address, State and County), item comments, item ranking, timestamp, activated(Y,N), user_level.

Then have a category column like

[category]
id
category

The field category_id would hold the numeric key to the category table.

You probably also want to do this with some of your other columns, i.e. have a user table with the username, password, address and user_level and then refer to that with an user_id field so:

[user]
id
username
password
address
user_level

so your initial table would simplify to
id, category_id, user_id, item_description, item_comments, item_ranking, timestamp, activated

Unless you want items to be able to be in different locations from the posters, then I'd say make an address table...

henry0

12:11 pm on Dec 12, 2006 (gmt 0)

Now I have a plan
Really appreciated your advices

It�s a tad early
However Happy holydays, Merry Christmas!
I am not politically correct but you may pick your own :)

physics

5:47 pm on Dec 12, 2006 (gmt 0)

Glad to help. Now that you're on the right track I'd also suggest you read up a bit on database design and normalization:
Database Design 101 - DatabaseJournal.com [databasejournal.com]