What kinds of tables do you use?

Anon

Hi,

I'm wondering what kinds of tables people use for their text data.

I'm building a site and storing the articles in TEXT fields - I don't have my computer with the specifics on right now, but I think TEXT medium.

But someone writing an article here on quick&dirty search engines for MySQL and PHP sites said that he stored his content in BLOBs.

What's best? What are the advantages or disadvantages?

I just started with MySQL about a month ago, and it's totally my first db experience ever, so I'd appreciate hearing what people are using, and giving me some guidance before I go live on what I should be using.

The last thing I want is to be live with most content in and start having problems with not enough room in my tables, etc.

Thanks!
-john

Anon

You shoul read the mysql manual and also you
have to know how big will be your text articles. If they are way bigger than 1mb then I reccomend you to use linking table.

Anon

Use the kind of column that maches your data closest.
if you need a space for a name, use something like a varchar(100)
If you need a yes/no field, use a boolean.
If you need a number, think of how big that number will be.
If you use an INT, and fill it with 24, you are wasting dbspace.

Read the MySQL manual for more info about the types.

Anon

article_id int(6) unsigned zerofill primary key auto_increment

title varchar(250)

abstract text (NOTE: this is where I key keywords that go in a meta tag page heads)

body mediumtext (this is the body of the article itself ... do I have enough room here for relatively standard web articles?)

category enum (a whole list of kinds of articles ... categories into which our articles fall)

publish enum ('n','y') (this is auto-set to n for new content, must be flipped over via a web-based control system by a content editor to Y for it to be published online

html enum ('n','y') (I want to submit some articles containing html, but I don't want non-technical ppl doing that. Auto-set to N, when set to Y there's different formatting procedures applied in eventual article)

date timestamp(12) (assigns current date to article ... used to determine article order on site)

author varchar(100)

email varchar(100)

password varchar(25) (I don't actually use this field right now, as I store this in another table.

Well, what do you think ... will this do me?

The main question I have is whether the body field, which is mediumtext, has enough room for my content articles. I think it does ... what does it contain, something like 200 K or so, which ought to be plenty for any and all articles I can think of.

But I'd like some second opinions.

Thanks again!
-john

Anon

Look like a good start to me.

Just a few points.
Mediumtext can hold 16MB. At around 3kbytes per screenfull of text, that looks a bit large :-)
But these fields only get as big as the data that's in them (plus a few bytes overhead) so don't wurry.

The part that does wurry me are the enum fields.
The last thing you want to do is have to change your database design lateron.
You are planning to use the enum type to define the type of the article.
Something tells me you will be adding more types lateron.
It may be a good idea to change that to something like smallint, which is a number between -32k and +32k
that gives you 32k of different types to use.
You can use that number to refer to a different table that holds the names of the types:
smallint TypeID, TypeDescription varchar(128)

(read the article about database normalization)
You can use a join statement to connect the two tables easily.

For the "publish" and "html_enum" you could also use the BOOL type.
This is a simple "true/false" type which does about the same in your particular application, but is smaller than the enum.

Just to make things more difficult; I guess you will be wanting to use a search function too. You can speed those up quite a bit by using indexes. Read about them in the manual.

Anon

Thanks for the tips - 'preciate it!

They all sound like good ideas to me.
-john