using fields with numeric names

9 Posts

MaXL Reply #1, 13 years, 11 months ago

Hi.

I have db table with fields, which names consists only number.
e.g.:

CREATE TABLE `modx_crm_azs` (
  -- ....
  `80` tinyint(4) NOT NULL DEFAULT '0',
  `92` tinyint(4) NOT NULL DEFAULT '0',
  `95` tinyint(4) NOT NULL DEFAULT '0',
  `98` tinyint(4) NOT NULL DEFAULT '0',
  `dt` tinyint(4) NOT NULL DEFAULT '0',
  `dtge` tinyint(4) NOT NULL DEFAULT '0',
  `sot` tinyint(4) NOT NULL DEFAULT '0',
  `wc` tinyint(4) NOT NULL DEFAULT '0',
  `active` tinyint(4) NOT NULL DEFAULT '1',
  `order` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`system_id`,`id`),
  KEY `isupdate` (`isupdate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I generated XML schema and model class for this table.
And, when I try to use it, I catch error.
Sorry, I can't post it here, because this situation happened a month ago,
and to repair this bug I rename my fields (only add some alphabetic characters).

My opinion about this bug is:
xPDO use array_merge function, when generate internal structures for my table.
And if array, which passed to array_merge has numeric keys, array_merge don`t promise to save it.
E.g. from php.net:

<?php
$array1 = array();
$array2 = array(1 => "data");
$result = array_merge($array1, $array2);
?>

Result:

Array
(
    [0] => data
)

Is there is really bug, or I am wrong ?

Excuse me for my terrible and lame English.
I want to believe that you understand me

[ed. note: MaXL last edited this post 13 years, 11 months ago.]

88 Posts

lazylegs Reply #2, 13 years, 11 months ago

This has to be first time I ever heard of numerical field names. With my experience would highly discourage use of them.

Still, like you mentioned there is a problem with the implementation with xPDO and database interaction. But then again, this is not a bug. I could say that: never name your fields just as numerics. This will also cause problems in the long run in so many places that there is no point to mention them.

Keep the field names sane, if the field names do not make sense and are just obscure stuff. There is more likely problem with the database design and normalization than with the DB layer.

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

9 Posts

MaXL Reply #3, 13 years, 11 months ago

Ok ;-)
For my experience this is first time too.
This is not my DB design.
But, see Schema Object Names, for more details about column names in MySQL.

But then again, this is not a bug. I could say that: never name your fields just as numerics.

:-) finally xPDO don`t support mysql. and this is nut a bug ?
Are you Linux user

? Is is a demonstration if typical behavior for them ))

793 Posts

ottogal Reply #4, 13 years, 11 months ago

Quoted from Schema Object Names:

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

So better read twice before getting personal. [ed. note: ottogal last edited this post 13 years, 11 months ago.]

9 Posts

MaXL Reply #5, 13 years, 11 months ago

may begin
:-)
So xPDO developers not mastered this feature.

793 Posts

ottogal Reply #6, 13 years, 11 months ago

may not consist solely of digits

)

☆ A M B ☆
24,524 Posts

sottwell Reply #7, 13 years, 11 months ago

(...unless quoted...)

However, this begs the question of whether or not it's a good idea. Like variable names and HTML classnames and ID attributes, table names and field names really should be expressive of their purpose. After all, not only will anyone else tasked with maintaining your code have to deal with it, but you may find yourself at some future time trying to figure out what that's supposed to be for.

Just out of curiosity, why would you want to do something like that?

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

9 Posts

MaXL Reply #8, 13 years, 11 months ago

may not consist solely of digits )

Yes, but unless quoted )))

First of all, there is not my DB structure.
In the first post I said: I rename my fields (only add some alphabetic characters).

Just out of curiosity, why would you want to do something like that?

This columns contains flag[0, 1], which describe existing type of petrol on
petrol-station (i.e. `92` == 1 means that '92 petrol' exists on petrol-station).

88 Posts

lazylegs Reply #9, 13 years, 11 months ago

Hmmm... got point there then again, this goes too deep even for my knowledge. But lets say we got 92, 95 and 98 octane fuels available + diesel etc...

This sounds more of many to many relation really in certain manner, as like what happened in Finland was that 95 is changing to E10 right now. So in sense it smarter to have tables: Station, gasoline_sold and gasolines. Where gasoline_sold is the many-to-many joiner. But then again, it ain't that straight forward. This would enable that you can add new fuel types without modifying the actual station table.

Also there comes new problems with the numbered fields. Have not tested this and most likely should (and might test it bit later) but if we look at from the PHP side. There could be loads of problems with PDO already. If we load an object Station. And try to get field 92 from it with $station->92 (here it should go horribly wrong already) as you cannot have variable starting with number in php. Even this behaviour is slightly transformed with xPDO, the same problem still exists.

Even the numbered field is allowed in MySQL I go with Susan also for that being "semantic" and have meaning with names is sane in my opinion. And with xPDO you can have aliases for horrific field names.

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

9 Posts

MaXL Reply #10, 13 years, 11 months ago

lazylegs this topic about numeric column name in mysql with xPDO and not about database design. Do you feel difference ?