how to manage products with different flags??

PHPycho

How do you handle product with different flags like featured, new, christmas special, valentine special..?

do you use fields in the same product table (sayroducts)
or different table
But How?

djjjozsi

product looks like in my example:
table name: product
fields: prod_id , productname,prise

You should have new tables: flags , where you list all the flags you need.
Flags table looks like:
tabel name: flags , flag_id, flag_name

You should make a new table, called
connector_product_flags

add two fields into this table (integer types):
product_id And flags_id

Means product_id will be equal with product's prod_id field, and flags_id will be equal with the flags table's flag_id

Now you make ID pairs into this table to list the product ID's and their flags in new single rows.

If you want to list the products and their flags, you have to JOIN the three tables.

for example:

SELECT product.prod_id,flags.flag_id,product.productname,product.prise,flags.flag_name
FROM flags INNER JOIN
(product INNER JOIN connector_product_flags ON product.prod_id = connector_product_flags.product_id)
ON flags.flag_id = connector_product_flags.flags_id
GROUP BY product.prod_id , product.productname,product.prise , flags.flag_id , flags.flag_name;

sneakyimp

I think by 'flag' you mean YES/NO or 0/1 or TRUE/FALSE. If your database is small, these can be used, but they create performance problems when you have a large number of records because queries you run that check a binary flag have to check EVERY SINGLE RECORD. In other words, indexes don't help when you have 'flag' fields in a table.

djjjozsi's recommendation to use a separate table is correct for two reasons:
1) your table doesn't end up with all kinds of flag fields in it that just make it big and messy and kind of confusing.

2) performance! this probably only matters for large data sets though.

johanafm

sneakyimp;10943857 wrote:
I think by 'flag' you mean YES/NO or 0/1 or TRUE/FALSE. If your database is small, these can be used, but they create performance problems when you have a large number of records because queries you run that check a binary flag have to check EVERY SINGLE RECORD.

Not if it is indexed. Granted, if half the rows have 0 and half the rows have 1 and this is index is being used for the query, half the rows would have to be checked (for other things in the where clause).

sneakyimp;10943857 wrote:
In other words, indexes don't help when you have 'flag' fields in a table.

Having multiple such flags would mean you'd need a multicolumn index.

sneakyimp;10943857 wrote:
2) performance! this probably only matters for large data sets though.

Performance should be better if you keep these flags in the same table and use a bit field approach with index, either through the SET datatype if it's available, or if you just use a plain unsigned int and deal with it as a bit set.
e.g

new = 1
featured = 2
christmas special = 4

To get various combinations

new & featured: flags = 3

new & christmas special: flags = 5

new & featured & christmas special: flags = 7

anything but new: flags & ~1

I don't know if the flags & ~1 would be able to use indexing, and it is possible that it would be able to for SET but not for unsigned int. On the other hand, doing the same with the normalized schema approach would require a subquery which also reduced performance. Profiling is always recommended for performance tuning.

And it should be noted that this approach means denormalizing the schema. However, it will still remain easy to update the field to remove a specific flag, which is not the case when using comma delimited strings.

-- clear new and featured flags for row where id = 2
UPDATE tbl SET flags = flags & ~3 WHERE id = 2

djjjozsi

If you store properties in the tbl table, you loose the opportunity
to track which user signed that new flags,
I usually track my flag's insertation date/time. How you count with a single query the flag's occurrence, or make tag cloud style output+query? How you handle if these flags are user generated contents? You need to hardcode a lot.

the normalized schema approach would require a subquery

INNER JOIN is a subquery? This is new for me.

johanafm

djjjozsi;10944040 wrote:
If you store properties in the tbl table, you loose the opportunity
to track which user signed that new flags,
I usually track my flag's insertation date/time. How you count with a single query the flag's occurrence, or make tag cloud style output+query?

Then it's obviously not an option, since these things aren't possible.

djjjozsi;10944040 wrote:
How you handle if these flags are user generated contents? You need to hardcode a lot.

It can still be handled, depending on what content and users we're dealing with. For example, there is an upper limit on the amount of flags that can be used.

But I'm not talking about removing the need for proper normalization on a general basis. I'm talking about the possibility of benefits in certain cases. In this case, I doubt they are goin to have blog tags for their products, but if they do they need to go in a separate table.

djjjozsi;10944040 wrote:
INNER JOIN is a subquery? This is new for me.

Once again, I was talking about a specific case, i.e. flags & ~1, which afaik has to be dealt with along the lines of

INNER JOIN flags ON flags.product_id = flags.id AND product_id NOT IN (SELECT product_id FROM flags WHERE flag = 1)

sneakyimp

I was skeptical of johanafm's assertions as it runs contrary to my prior experiments. so I did some benchmarking.

I took a customers table with 37638 records from an oscommerce database. It has numerous fields in it so it represents realistic data. I added 5 flag fields (of type tinyint[1]) and randomly set 1 in 10 of these flags to "1".

I also created an assoc table:

CREATE TABLE `foo_assoc` (
  `customers_id` int(11) unsigned NOT NULL,
  `flag_number` tinyint(4) unsigned NOT NULL,
  KEY `customers_id` (`customers_id`,`flag_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I created two separate indexes -- one on each field. If one of the flags was set in the 'foo' table then I also added a record to this table so they essentially encode the same information. If flag3 is "1" in foo, then I insert a record with flag_number=3 in foo_assoc.

NOTE: I repeatedly ran this to clear the query cache.
RESET QUERY CACHE;

I ran these queries
SELECT FROM foo WHERE flag1=1 (0.0030 sec)
SELECT FROM foo WHERE flag2=1 (0.0030 sec)
SELECT FROM foo WHERE flag3=1 (0.0031 sec)
SELECT FROM foo WHERE flag4=1 (0.0029 sec)
SELECT * FROM foo WHERE flag5=1 (0.0030 sec)

When I added indexes on each individual flag field, the times improved. Indexes do apparently help.
SELECT FROM foo WHERE flag1=1 (0.0008 sec)
SELECT FROM foo WHERE flag2=1 (0.0008 sec)
SELECT FROM foo WHERE flag3=1 (0.0008 sec)
SELECT FROM foo WHERE flag4=1 (0.0008 sec)
SELECT * FROM foo WHERE flag5=1 (0.0008 sec)

I then tried queries using the assoc table:
SELECT f. FROM foo f INNER JOIN foo_assoc fa ON fa.customers_id=f.customers_id AND fa.flag_number=1; (0.0010 sec)
SELECT f. FROM foo f INNER JOIN foo_assoc fa ON fa.customers_id=f.customers_id AND fa.flag_number=2; (0.0010 sec)
SELECT f. FROM foo f INNER JOIN foo_assoc fa ON fa.customers_id=f.customers_id AND fa.flag_number=3; (0.0010 sec)
SELECT f. FROM foo f INNER JOIN foo_assoc fa ON fa.customers_id=f.customers_id AND fa.flag_number=4; (0.0010 sec)
SELECT f.* FROM foo f INNER JOIN foo_assoc fa ON fa.customers_id=f.customers_id AND fa.flag_number=5; (0.0010 sec)

I also tried these:
SELECT customers_id FROM foo WHERE flag1=1 (0.0007 sec)
SELECT customers_id FROM foo_assoc WHERE flag_number=1 (0.0003 sec)
SELECT f.customers_id FROM foo f INNER JOIN foo_assoc fa ON fa.customers_id=f.customers_id AND fa.flag_number=1; (0.0005 sec)

I think it's worth noting that the assoc table approach works a bit faster if you are only grabbing a teeny bit of data. I tried setting 50% of the flags to 1 and then 90% of the flags to 1 and the results are pretty much the same.

I tried the same thing with a bigger table that has more fields. It was the orders table with 308991 records and it takes up about 100 MB on disk including the indexes I've added.

SELECT FROM bar WHERE flag1=1 (0.0013 sec)
SELECT b. FROM bar b INNER JOIN bar_assoc ba ON ba.orders_id=b.orders_id AND ba.flag_number=1; (0.0016 sec)
SELECT orders_id FROM bar WHERE flag1=1 (0.0007 sec)
SELECT b.orders_id FROM bar b INNER JOIN bar_assoc ba ON ba.orders_id=b.orders_id AND ba.flag_number=1; (0.0006 sec)

These timings really don't change much if 10% of the flags are set or 90% of them.

SUMMARY: For the smallish table sizes we're talking about here, Johanafm is right. I believe (and could be totally wrong) that when we're talking about much bigger db tables (say 1GB or 10G😎, the flag approach will cause problems because a disk drive will not be able to cough up the entire table fast enough to allow inspection of a flag column which it must extract from all the other column values in a given database record.

It's also been my experience that any query which requires a function to be performed on each record kills performance because it precludes the use of an index. In other words, this query can't use the index for some_indexed_field because it's checking the results of a function and cannot predict the outcome based on the index contents.

SELECT field1, field2 FROM some_table WHERE SOME_FUNCTION(some_indexed_field) = 1;

There are other considerations as well. With an association table, you might have to perform extra JOINs or extra queries to maintain the extra table. With flag fields in the original table, you might have to continue changing your table definition as you add fields or you may run out of bits if you use the mask approach.

This is probably more info then PHPycho ever wanted.

johanafm

Thanks for posting the profiling data, sneakyimp. But I never meant to advocate the use of one field per flag over a normalized structure. I'm talking about the possible use of bitflags to break normalization in order to simplify and speed up certain queries. And you are correct to assume that it does become harder to deal with changing the underlying structure, as well as performing certain queries (such as keeping timestamps for changes to bitflags, which would have to be dealt with in a separate table anyway). It should in other words not be done without reason.

But, to clarify what I mean I created one table with bitflags

CREATE TABLE name_and_flags (
	id	INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name	VARCHAR(10),
	flags	SET('new', 'featured', 'christmas special', 'easter', 'sale','limited edition')
-- forgot to add index for flags, so modified this afterwards
ALTER TABLE name_with_flags ADD index (flags)

and a three table normalized equivalent

CREATE TABLE IF NOT EXISTS name (
	id	INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name	VARCHAR(10))

CREATE TABLE flag (
	id	INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	flag	VARCHAR(20))
INSERT INTO flag(flag) VALUES('new'), ('featured'), ('christmas special'), ('easter'),
('sale'), ('limited edition')

CREATE TABLE name_has_flag (
	name_id INT UNSIGNED,
	flag_id INT UNSIGNED,
	PRIMARY KEY(name_id, flag_id),
	FOREIGN KEY(name_id) REFERENCES name(id) ON DELETE CASCADE,
	FOREIGN KEY(flag_id) REFERENCES flag(id) ON DELETE CASCADE)

I then filled the tables with data consisting of 100k names having no flag, and roughly 100k names having any combination of flags (other than none). Also, the normalized approach represented the same data as the non-normalized one.
The "roughly" part comes from me starting the script with a higher setting than 100k and ending up aborting the process.

function createName() {
 	$name = '';
	for ($i = 0; $i < 8; ++$i) {
		$name .= chr(rand(65, 93));
	}
	return $name;
}
function createFlag($max = 63) {
	return rand(0, $max);
}
function flagArray($flag, $max = 32) {
	$arr = array();
	for ($i = 1, $flag_val = 1; $i <= 32; $i *= 2, ++$flag_val) {
		if ($flag & $i)
			$arr[] = $flag_val;
	}
	return $arr;
}

// Fill tables
# Without flags
$nf = $db->prepare('INSERT INTO name_and_flags(name, flags) VALUES(?,0)');
$n = $db->prepare('INSERT INTO name(name) VALUES(?)');
for ($i = 0; $i < 100000; ++$i) {
	$name = createName();
	$nf->bind_param('s', $name);
	$nf->execute();
	$n->bind_param('s', $name);
	$n->execute();
}

# With flags
$nf = $db->prepare('INSERT INTO name_and_flags(name, flags) VALUES(?,?)');
$n_has_f = $db->prepare('INSERT INTO name_has_flag VALUES(?, ?)');
for ($i = 0; $i < 150000; ++$i) {
	if ($i % 100 == 0)
		echo $i . PHP_EOL;
	$name = createName();
	$f = createFlag();
	# table name_and_flags (with both name and flags)
	$nf->bind_param('si', $name, $f) or die($nf->error);
	$nf->execute() or die($nf->error);

# table name (name only)
$n->bind_param('s', $name) or die($n->error);
$n->execute() or die($n->error);
$nid = $n->insert_id;

# table name_has_flag (link table between name and flag
$flags = flagArray($f);
foreach($flags as $flag) {
	$n_has_f->bind_param('ii', $nid, $flag) or die($name_has_flag->error);
	$n_has_f->execute() or die($n_has_f->error);
}
}

And the result

[b]flags = 7[/b]
0.0019s for query SELECT count(id) FROM name_and_flags WHERE flags = 7
0.4031s for query SELECT count(name_id) FROM (SELECT nhf1.name_id FROM name_has_flag AS nhf1 INNER JOIN name_has_flag AS nhf2 ON nhf1.name_id = nhf2.name_id AND nhf2.flag_id = 2 INNER JOIN name_has_flag AS nhf3 ON nhf1.name_id = nhf3.name_id AND nhf3.flag_id = 3 WHERE nhf1.flag_id = 1 AND nhf1.name_id NOT IN (SELECT name_id FROM name_has_flag WHERE flag_id > 3) ) AS t

[b]flags & 7 = 7[/b]
0.0904s for query SELECT count(id) FROM name_and_flags WHERE flags & 7 = 7
0.3647s for query SELECT count(name_id) FROM (SELECT nhf1.name_id FROM name_has_flag AS nhf1 INNER JOIN name_has_flag AS nhf2 ON nhf1.name_id = nhf2.name_id AND nhf2.flag_id = 2 INNER JOIN name_has_flag AS nhf3 ON nhf1.name_id = nhf3.name_id AND nhf3.flag_id = 3 WHERE nhf1.flag_id = 1 ) AS t

[b]flags & 7[/b]
0.0893s for query SELECT count(id) FROM name_and_flags WHERE flags & 7
0.2509s for query SELECT count(name_id) FROM (SELECT distinct name_id FROM name_has_flag WHERE flag_id = 1 OR flag_id = 2 OR flag_id = 3) AS t

[b]not flags & 1[/b]
0.095s for query SELECT count(id) FROM name_and_flags WHERE NOT flags & 1
1.5292s for query SELECT count(id) FROM name WHERE id NOT IN (SELECT name_id FROM name_has_flag WHERE flag_id = 1)

[b]no flag[/b]
0.0921s for query SELECT count(id) FROM name_and_flags WHERE NOT flags = 0
1.0087s for query SELECT count(id) FROM name WHERE id NOT IN (SELECT name_id FROM name_has_flag)

sneakyimp

I'm not at all certain, but I believe that all advantages of indexing are broken when you use a subquery.

Also, the use of binary operators (e.g., "flags & 7") may require a db engine to perform an operation on every record -- this may also compromise any advantages acquired by indexing.

Perhaps you could try INNER JOINs instead? I'd wager those would be faster than the subquery approach -- provided you created your indexes correctly.

johanafm

You are correct about index not being used when logical operators are involved. In my queries above, only flags = 7 used an index. Still, this approach is faster than using subquery and I fail to see how it would be possible to solve without subqueries.

Moreover, I fail to see how partitioning would solve some of these queries, since you'd still have to scan all partitions to find out wether more than one flag exists. But perhaps it's my lack of knowledge on appropriate data structures for this problem that gets me to this conclusion.

However, I havn't encountered these situations that often. On a general basis, the normalized schema is definitely the way to go. But for certain situations the flag approach is still the best I've found.

sneakyimp

I've tried to formulate join queries that return identical results. It's important to keep in mind that you are limited to some fixed number of joins by your dbms, so it may be that the mask approach can probably handle more flags. once you get past around 8 flags, I think you'll be reaching a limit in the number of tables you can join. I don't recall what this limit is, but I believe it's considerably less than 32 which is the number of bits in an integer.

Let's take your first query:

SELECT count(name_id) FROM (SELECT nhf1.name_id FROM name_has_flag AS nhf1 INNER JOIN name_has_flag AS nhf2 ON nhf1.name_id = nhf2.name_id AND nhf2.flag_id = 2 INNER JOIN name_has_flag AS nhf3 ON nhf1.name_id = nhf3.name_id AND nhf3.flag_id = 3 WHERE nhf1.flag_id = 1 AND nhf1.name_id NOT IN (SELECT name_id FROM name_has_flag WHERE flag_id > 3) ) AS t

This should return essentially the same results and does not require a subquery. Note that I removed the COUNT() bit because phpMyAdmin doesn't report execution times if you do. The results are actually limited to 30 results so imagine all end with LIMIT 0,30. Also, I have added an underscore to the beginning of the tables' names to distinguish them from a mess of other tables i have in this db.

SELECT nhf1.name_id
FROM _name_has_flag nhf1
INNER JOIN _name_has_flag nhf2
ON (nhf2.name_id=nhf1.name_id AND nhf2.flag_id=2)
INNER JOIN _name_has_flag nhf3
ON (nhf3.name_id=nhf1.name_id AND nhf3.flag_id=3)
LEFT JOIN _name_has_flag nhf4
ON (nhf4.name_id=nhf1.name_id AND nhf4.flag_id >3)
WHERE nhf1.flag_id=1
AND nhf4.name_id IS NULL

It executes in 0.0120 sec on my iMac. Your equivalent query runs a much faster .0005:

SELECT id FROM `_name_and_flags` WHERE flags=7

The other queries appear to work faster with the joins I've written. The result counts are identical for both queries -- I made a correction to your last query because they were doing different things. Your original query was getting 'any flag' for the mask approach and 'no flags' for the subquery approach. I altered both queries and they should now return NO FLAGS.

flags & 7 = 7
0.0278 sec for SELECT id FROM _name_and_flags WHERE flags & 7 = 7
0.0015 sec for SELECT nhf1.name_id FROM _name_has_flag nhf1 INNER JOIN _name_has_flag nhf2 ON (nhf2.name_id=nhf1.name_id AND nhf2.flag_id=2) INNER JOIN _name_has_flag nhf3 ON (nhf3.name_id=nhf1.name_id AND nhf3.flag_id=3) WHERE nhf1.flag_id=1

flags & 7
0.0250 for SELECT id FROM _name_and_flags WHERE flags & 7
0.0005 for SELECT DISTINCT nhf1.name_id FROM _name_has_flag nhf1 WHERE flag_id IN (1,2,3)


not flags & 1
0.0028 for SELECT id FROM _name_and_flags WHERE NOT flags & 1
0.0004 for SELECT n.id FROM _name n LEFT JOIN _name_has_flag nhf ON (nhf.name_id=n.id AND nhf.flag_id=1) WHERE nhf.name_id IS NULL;


no flag
0.0005 for SELECT id FROM _name_and_flags WHERE flags = 0
0.0003 for SELECT id FROM _name n LEFT JOIN _name_has_flag nhf ON nhf.name_id=n.id WHERE nhf.name_id IS NULL;

When you have VERY VERY VERY LARGE DATA FILES (i.e., where the data file for a single table is measured in gigabytes) then the use of indexes becomes absolutely critical. Sxooter was very kind to help me in that other thread where I was dealing with tables that had 10 or 100 million records in them and numerous columns in each record. When you have enormous files and lots of columns, just getting to a particular column in a particular record is a chore in itself. Because of this, any queries that performed a calculation on every record were extremely slow because the disk drive had to serve up the entire file and the cpu had to find the byte offset of the data of interest and then the calculation had to be performed and a temporary table created, etc. It was quite slow and partitioning and indexes were really the only way to appreciably improve performance.

to be honest, i'm surprised the tinyint flags with indexes worked so fast in my prior post.