[RESOLVED] Too many variables!

mborish_2000

I’m trying to create a mysql table that I can input and retrieve data from. The concept of the table is simple but populating the table and retrieving the data confuses the hell out of me. Basically, for each row, I’ve got a unique identifier in its own column and a bunch of variables associated with the identifier in the same column. I’m going to have to use preg_replace() and some other tricks to get the variables into an array and different columns, but I think that this should do for a brief example. No matter what, I’ve got at least one variable associated with a unique identifier, but I could have an infinite amount of variables associated with any unique identifier. What I want to do is stuff each variable into its own column and then retrieve the identifier by searching through the associated variables and then retrieve all of the associated variables that are indexed by that specific identifier. What I can’t figure out is the best way to name all of the columns and variables or retrieve them? Hopefully, somebody can suggest some cool trick? The raw data set looks like this:

Unique identifier Variables
2005123413214 20-16-105-034 & 20-16-104-035
0146546487831 15-14-230-155
2465454878646 15-14-569-233
2456545478979 14-24-553-123 & 14-24-553-123, 23-17-105-026

$query = "SELECT * FROM tablename WHERE $field[0] LIKE \"$variable%\" OR $field[1] LIKE \"$variable%\" OR $field[2] LIKE \"$variable%\" ORDER BY $field[0] DESC" ; // ….?

Piranha

You should search the web for "database normalisation", then you will understand why your database is built wrong. Can two variables have the same value? If it can, then you should use 3 tables, otherwise 2 would be enough. 3 tables should be built like this:

CREATE TABLE identifier (
id INT PRIMARY KEY
// Other columns that have to do with the id
)

CREATE TABLE variables (
variable VARCHAR(13) PRIMARY KEY
// Other columns that have to do with the variable
)

CREATE TABLE identifier_variables (
identifier_id INT,
variable_variable VARCHAR(13),
PRIMARY KEY (identifier_id, variable_variable),
FOREIGN KEY (identifier_id) REFERENCES identifier(id),
FOREIGN KEY (variable_variable) REFERENCES variable(variable)
)

mborish_2000

What I'm trying to do here is extract all data from the variables column and crate Non First Normal Form (NF²) relations with php and MYSQL. There is a small possability that some variables will be duplicated but it is not logical to normalize them.

Also, I don't think you understood me. I could realistically have 20 up to 1000? or so variables in the variables column that I am going to extract. You're suggesting I should create 1000 plus foreign keys by hand?

All variables reference the unique identifier ID.

Piranha

Not at all. I suggest that you normalise your database, creates a database table with all the identifiers, one with all the variables and one with all the relationships. In other words 3 tables that you have complete control over instead of 1 table that you will bet in trouble trying to handle.

But since you say that you don't want to normalise I won't try to help you out further. I wish you good luck trying to handle it, maybe someone else can help you out.

Edit: I still think that you should normalise, it is only in very special cases normalisations should not be done. Can you please tell why you don't want to normalise, I'm really curious.

mborish_2000

I understand. Your idea has merit. I will try it later.

Piranha

Now you really got me curious. You first say that you design it not to do it in NF2 and that it is not logical to normalize them. Then you say that my idea have merit. It seems like you wanted to make it as easy as possible by not normalising. If it is that way I am curious about why there would be such a thing as normalisation if it was not intended to make it easier for people.

I know this post sounds rude. It is not my intention, but I just can't think of another way to ask.

mborish_2000

Sorry if I'm all over the place with this stuff; I'm working with a huge amount of data. I'm somewhat new to database normalization. I read some tutorials and stuff but it never occurred to me to create three tables instead of one. I guess I'm having trouble thinking outside of the box, plus I'm scared of managing too many tables. I'm going to create a test program later and see how things work out.

mborish_2000

I did some research and tried to create the tables with phpMyAdmin. For some reason, the primary key in the mtg_pin_identifier table does not reflect doc_id or pin_id. Is this still correct? Here's what I've go so far:

CREATE TABLE `mtg_doc` (
  `doc_id` tinyint(4) NOT NULL auto_increment,
  `doc` int(11) NOT NULL,
  PRIMARY KEY  (`doc_id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;

CREATE TABLE `mtg_pin` (
  `pin_id` tinyint(4) NOT NULL auto_increment,
  `pin` text NOT NULL,
  PRIMARY KEY  (`pin_id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;

CREATE TABLE `mtg_pin_identifier` (
  `identifier_id` tinyint(4) NOT NULL auto_increment,
  `doc_id` tinyint(4) NOT NULL,
  `pin_id` tinyint(4) NOT NULL,
  PRIMARY KEY  (`identifier_id`),
  FOREIGN KEY (`doc_id`) REFERENCES `mtg_doc` (`doc_id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
  FOREIGN KEY (`pin_id`) REFERENCES `mtg_pin` (`pin_id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
) TYPE=InnoDB AUTO_INCREMENT=1 ;

If this structure is correct, disregarding the number of integers, how do I insert data into this thing from a small test file?

mborish_2000

For some reason, phpMyAdmin won't let me create the mtg_pin_identifier table??

mborish_2000

OK, so I tried tinkering with things and here is what I have now:

CREATE TABLE `mtg_pin_identifier` (

`identifier_id` INT NOT NULL AUTO_INCREMENT ,
`doc_id` INT NOT NULL ,
`pin_id` INT NOT NULL ,
PRIMARY KEY ( `identifier_id, doc_id` ) ,
FOREIGN KEY ( `doc_id` ) REFERENCES `mtg_doc` ( `doc_id` ) ON DELETE CASCADE ON UPDATE CASCADE ,
FOREIGN KEY ( `pin_id` ) REFERENCES `mtg_pin` ( `pin_id` ) ON DELETE CASCADE ON UPDATE CASCADE 
) TYPE = InnoDB AUTO_INCREMENT =1

This is the error that I get: #1072 - Key column 'identifier_id, doc_id' doesn't exist in table. The other tables were created successfully.

Piranha

PRIMARY KEY ( identifier_id, doc_id )

But I don't understand your PK. I would do it like this:

CREATE TABLE `mtg_pin_identifier` (
`doc_id` INT NOT NULL ,
`pin_id` INT NOT NULL ,
PRIMARY KEY ( `doc_id`, `pin_id` ) , 
// and the rest

mborish_2000

I don't have a clue what I'm doing here. I can't get this thing to work. I can get the first two queries to process, but the last one, $query3, doesn't go through. I get: #1005 - Can't create table './mborish2000/mtg_pin_identifier.frm' (errno: 150)

Here is my code:

$query1 = "CREATE TABLE `mtg_doc` (
	   `doc_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	   `doc_number` INT NOT NULL
) ENGINE = INNODB";

$query2 = "CREATE TABLE `mtg_pin` (
		`pin_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
		`pin_number` INT NOT NULL
) ENGINE = INNODB";

$query3 = "CREATE TABLE `mtg_pin_identifier` (
		`doc_id` SMALLINT NOT NULL,
		`pin_id` SMALLINT NOT NULL,
		PRIMARY KEY (`doc_id`, `pin_id`),
		FOREIGN KEY (`doc_id`) REFERENCES `mtg_doc` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE ,
		FOREIGN KEY ( `pin_id` ) REFERENCES `mtg_pin` (`pin_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB";

mysql_query($query1) or die(mysql_error());
mysql_query($query2) or die(mysql_error());
mysql_query($query3) or die(mysql_error());

What is the point of having both the doc_id and pin_id in the primary key?

mborish_2000

Um.... I'm not sure if anybody cares or not, but I actually figured it out - for now anyways. I'm going to load data tomorrow and maybe I'll be back but hopefully not. Thanks to everyone that helped. BTW The stupid problem was that I DIDN'T INDEX the column before each row. I tried joining them together like this,before my foreign keys, INDEX(pin_id, doc_id) a long time ago and no luck. Breaking them down solved it? Here's the code:

$query1 = "CREATE TABLE mtg_doc (
	   doc_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	   doc_number INT NOT NULL
) ENGINE = INNODB";

$query2 = "CREATE TABLE mtg_pin (
		pin_id INT PRIMARY KEY AUTO_INCREMENT,
		pin_number INT NOT NULL	
) ENGINE = INNODB";

$query3 = "CREATE TABLE mtg_pin_identifier (
		identifier_id INT NOT NULL AUTO_INCREMENT,
		doc_id INT NOT NULL,
		pin_id INT NOT NULL,
		PRIMARY KEY(identifier_id),
		INDEX (doc_id),
		FOREIGN KEY (doc_id) REFERENCES mtg_doc (doc_id) ON DELETE CASCADE ON UPDATE CASCADE,
		INDEX (pin_id),
		FOREIGN KEY (pin_id) REFERENCES mtg_pin (pin_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB";

Piranha

mborish_2000 wrote:
What is the point of having both the doc_id and pin_id in the primary key?

The point is that those two together creates the primary key. In other words, you can only have one row with one combination. Some people like to have another PK, and that is good as well. But to do the same thing they have to make sure to do the ids unique as well to avoid possible problems.

Basically it's a matter of taste, there is really no preferred way.