Best Aproach to Store Different Possible Values For Single Item

Databases
1

I have this table schema at the moment:


CREATE TABLE MCP_VIEWS_ARGUMENTS (
	id BIGINT UNSIGNED NOT NULL auto_increment
	,parent_id BIGINT UNSIGNED NULL
	,view_id BIGINT UNSIGNED NOT NULL
	,arg_name VARCHAR(128) NOT NULL	
	,value_get VARCHAR(128) NULL
	,value_post VARCHAR(128) NULL	
	,value_args TINYINT UNSIGNED NULL
	,value_args_absolute TINYINT UNSIGNED NULL	
	,value_func VARCHAR(128) NULL
	,value_func_args LONGTEXT NULL	
	,value_dao VARCHAR(255) NULL
	,value_dao_method VARCHAR(255) NULL
	,value_dao_args LONGTEXT NULL	
	,value_eval LONGTEXT NULL
	,required TINYINT UNSIGNED NOT NULL DEFAULT '0'
	,PRIMARY KEY(id)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

I’m not happy with it really.

The idea is that an argument, represented by a single row can be resolved to one of several places.

  • get array
  • post array
  • internal URL arguments; ie. /1/2/3/4
  • function w/ serialized arguments being passed
  • dao method call w/ arguments being passed
  • php snippet to be evaled

So I am attempting to figure out the best to support all those possible scenarios, though only a single one will ever exist. Preferably without adding a whole bunch of extra tables per each scenario as that will get messy real quick.

Similarly I have another table:


CREATE TABLE MCP_VIEWS_FILTER_VALUES (
	id BIGINT UNSIGNED NOT NULL auto_increment
	,filters_id BIGINT UNSIGNED NOT NULL
	,value_static TEXT NULL
	,value_argument_id BIGINT UNSIGNED NULL
	,value_field_id BIGINT UNSIGNED NULL
	,wildcard ENUM('%s%','%s','s%') NULL
	,regex VARCHAR(255) NULL
	,PRIMARY KEY(id)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

Which is a little similar though a little less messy. In that case there are three possible scenarios for value_*.

  • static text string
  • foreign key reference to the argument table above
  • foreign key reference to a field (another table in the system)

Perhaps there is a a better way to do this also. Though the same concept here is shared across several tables.

The SQL posted above it pseudo code at this point, to be refined once a proper design is established for what I’m trying to accomplish.

thanks

2

bump

I can’t see myself using the upper-most schema, its just hacky as hell. Any less hacky schema approaches?

3

i think you overlooked explaining what those columns are supposed to contain

if it is some magical “uh-oh” stuff (i.e. if it would be obvious to any OO programmer), then maybe that’s why i don’t know what you’re doing

4

It is some magical “uh-oh” stuff. An argument is a reference to derive data to embed in a SQL query. How or in what context the value is derived is the major problem. There are several separate contexts that need to supported such as; from GET, POST arguments or function/method call that will return the value. The most simple of these cases is a static value. In the case of a static value the value is as defined and isn’t derived using a dynamic source such as; function, method, etc. The second part of the problem was making sure the value is correctly embedded in the SQL query depending on its data type. In any case I have decided the below gets me to the goal, without being to convoluted or verbose. I may separate out the enums into a separate table though they are pretty much set in stone.

  • If familiar with Drupal Views its the same concept with ability to define arguments at the top and reference them within filters, sorting and column selections.

CREATE TABLE MCP_VIEW_ARGUMENTS (
	 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT	
	,parent_id BIGINT UNSIGNED NULL COMMENT 'foreign key to argument in which one argument overrides the value of another, but not the type or context'	
	,view_id BIGINT UNSIGNED NOT NULL COMMENT 'foreign key to view argument belongs to'
	,system_name VARCHAR(128) NOT NULL COMMENT 'Name of argument'
	,human_name VARCHAR(128) NOT NULL COMMENT 'title/label of argument'
	,value LONGTEXT NOT NULL COMMENT 'This may be a class name, function name, get ref, post ref, view ref, static value, etc based on the context. The context will determine how the value is handled within the application.'
	,type ENUM('string','int','bool','float') COMMENT 'The value type to cast the string to on the application end'
	,context ENUM('static','post','get','request','global_arg','module_arg','dao','function','class','view') NOT NULL
	,context_routine VARCHAR(128) NULL COMMENT 'The function or method name to call to derive the true value for dao, class and function contexts'
	,context_args LONGTEXT NULL COMMENT 'Serialized array of arguments to pass to a method or function call for dao, function and class context'
	,required TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'For context other than static whether the argument is required to build the view'	
  	,removed TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Determines whether argument has been removed for a overriding view'
  	,updated_on_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  	,created_on_timestamp TIMESTAMP NULL DEFAULT NULL
  	,deleted_on_timestamp TIMESTAMP NULL DEFAULT NULL
  	,deleted TINYINT UNSIGNED DEFAULT '0'
	,PRIMARY KEY(id)
	,UNIQUE KEY(view_id,system_name,deleted)
	,UNIQUE KEY(view_id,human_name,deleted)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Contains arguments that may be referenced via select options, filter values and sorting priorities when building views that require dynamically derived data references.';
5

the “maybe” i don’t know what you’re doing has turned into a “definitely” i don’t know what you’re doing

:o

6

I barely understand what I’m doing as well :wink: