Which is Better?

Anon

Hi,

Im at a bit of a puzzeling situation.

I have my main News Table.

I also have a NewsArchive Table.

I am currently moving Rows from News to NewsArchive, when the archive option is selected.

Wouldn't it be better if there was a BOOLEAN Field within the First Table to set Whether an item is Archived or Not, and then sort it out from there rather than having an extra table.

Coz I dont know if having more fields effects the table, or if seperating tables increases the search time for those who are searching archives, and those who are searching Recent Articles.

any help appreciated

-regards-
UniMatrix

nevermore2181

If you're using MySQL, then you really don't have a problem. It's clean and efficient... but that doesn't mean you can be sloppy either. If you do put them into a single table, make sure to set the 'Archive' field as the primary key.
Seperating the data is a good idea though, because the first table will obviously be accessed far more often than the archived data will be, however you then run into issues with keeping the two tables properly synchronized, and large functions just to transfer the data.
Really, it's a trade-off.

Anon

Thanx, for your insight....

Sloppy coding is kinda right...that defines me.

LOL 🙂 l8rs

-regards-
UniMatrix

majik-sheff

Close there, you can't make a boolean field a primary key (unless you only have 2 items in your database).
You need to index the archive field, with duplicates allowed. Then either use a timestamp as the primary key or an auto-increment number.

andrin

Note that MySQL 3.X does not support true BOOLEAN. You could use ENUM with true and false but a true BOOLEAN is not there :-(