Too many fields?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tsg
Forum Contributor
Posts: 142
Joined: Sun Jan 12, 2003 9:22 pm
Location: SE, Alabama
Contact:

Too many fields?

Post by tsg »

hi all,

I have a project I am working on for a client where she can basically offer different languages on her website. So I have created a table that holds different phrases and words.

Right now I have about 200 fields, and need to add about another 100.

Can there be too many fields (not rows)? Also, would having 300 fields in a table slow it down? Basically i would just query the table (*) and display the variables where needed.

Thanks!
Tim
tsg
Forum Contributor
Posts: 142
Joined: Sun Jan 12, 2003 9:22 pm
Location: SE, Alabama
Contact:

Post by tsg »

I should add 95% of the feilds are varchar(255)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

that many fields can certainly slow it down if you're selecting all of them. I'd probably go for a more normalized approach where the table has maybe four fields: a table ID, a language ID, a phrase ID and the phrase itself.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

feyd wrote:that many fields can certainly slow it down if you're selecting all of them. I'd probably go for a more normalized approach where the table has maybe four fields: a table ID, a language ID, a phrase ID and the phrase itself.
Personally I'd make my phrase table keyed on Phrase ID and Language.

Code: Select all

CREATE TABLE T_LITERALS (
ID_LITERAL VARCHAR(50) NOT NULL,
ID_LANG CHAR(2) NOT NULL,
DE_LITERAL VARCHAR(255) NOT NULL,
PRIMARY KEY (ID_LITERAL, ID_LANG));
in this way, you can put a readable description of the text (for example HOMEPAGE_TITLE), the language (eg: EN), and the phrase in the language ("Welcome to my website!")

So, for example, your table might look like:

Code: Select all

HOMEPAGE_WELCOME     EN     Welcome to my website!
HOMEPAGE_WELCOME     IT     Benvenuto al mio sito!
HOMEPAGE_WELCOME     DE     Wilkommen im meine Website!
...
Then, when you call these texts from your scripts, it is more readable because you can create a function that does it, and you can call it with: getLiteral("HOMEPAGE_WELCOME", "{$_SESSION['user_language']}");
Post Reply