Re: Database Design and Bianry Operations - Mojtaba Faridzad - com.mysql.lists.mysql

5 messages in com.mysql.lists.mysqlRe: Database Design and Bianry Operat...

From	Sent On	Attachments
Lee Denny	14 Oct 2003 06:17
Kevin Gale	14 Oct 2003 06:27
Mikhail Entaltsev	14 Oct 2003 06:33
Tom Horstmann	14 Oct 2003 07:23
Mojtaba Faridzad	20 Oct 2003 06:51

Subject:	Re: Database Design and Bianry Operations
From:	Mojtaba Faridzad (mfar...@pylonelectronics.com)
Date:	10/20/2003 06:51:37 AM
List:	com.mysql.lists.mysql

by 4 bytes number (as 1 field) , you can keep 32 bits (criteria). this shows how much you can save the space. but if the database is not huge, it's better to select simpler solution (one field for one criteria). this is an example in MySQL manual to show how to work with bits:

The following shows an idea of how you can use the bit group functions to calculate the number of days per month a user has visited a web page.

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23);

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;

Hello,

I'm after some advice on database design:

I've got an object - for an example a hotel - and I want to keep information about this hotel, criteria that it either has or hasn't (TV, swimming pool etc).

I want to search on criteria and return the most appropriate match.

Bearing in mind I've currently got over 200 criteria and want to expand this, how should approach the design of my criteria table.

Should I have a table with Hotel Id and then a char(1) (Y/N) field for each criteria and then a seperate look-up table for criteria name.

I get the feeling there is a more efficient method using binary operations (only one field populated with zeros and ones) but I can't find anything to help in the manuals.

Any thoughts?