CODEWITHSUNDEEP
mysql
RonSper
RonSper

Reputation: 693

Storing attribute/option data without EAV

I am making a local cafe listing website and I wanted to get some feedback on my database structure.

The cafes have a bunch of attributes that they can select one or many on any option. Here is how I am planing on storing their data.

TABLE: 'cafes'

CREATE TABLE `cafes` (
  `cafe_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `cafe_types` text NOT NULL,
  `location_types` text NOT NULL,
  `amenities` varchar(255) NOT NULL,
  `parking` varchar(255) NOT NULL,
  PRIMARY KEY (`cafe_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

TABLE: 'cafes_option_map'

CREATE TABLE `cafes_option_map` (
  `map_id` int(11) NOT NULL AUTO_INCREMENT,
  `column_name` varchar(30) NOT NULL,
  `cafe_id` int(11) NOT NULL,
  `name_id` int(11) NOT NULL,
  PRIMARY KEY (`map_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

TABlE: 'cafes_option_name'

CREATE TABLE `cafes_option_name` (
  `name_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`name_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Columns cafe_types, location_types, amenities, and parking are multi-selects with many "options". These options need to be sorted and filtered by.

My questions are:

  1. is this a good why of storing the data?
  2. Would there be a better way to do it.

My idea is that the 'cafes' table will contain a comma separated list of the attribute names (like human legible) for easy display with minimal DB calls and then also have the option table for filter and sorting queries.

I can make some sample data for the tables if that is easer to understand.

How would you suggest you store such data? I don't want a EAV structure.

Upvotes: 2

Views: 164

Answers (1)

Johan
Johan

Reputation: 76577

is this a good why of storing the data?

Hell no!

CSV is evil
CSV cannot be indexed in a sane manner.
Matching on CSV's is slow.
Joining on CSV's is a nightmare.
Once you start using them you will get bold, because you will start to pull out your hair every time you need to write a new query.

Would there be a better way to do it.

EAV would be much, much better than CSV.

But I don't want EAV

Good for you, seriously. EAV is just a stop gap measure.
If you want to link n-attributes to n-cafes, you have a n-n relation and that requires a join table. 

table cafe
------------
id integer PK autoincrement
name
address

table cafe_type
----------------
id integer PK autoincrement
name

table cafetype_link
-------------------
cafe_id integer
cafe_type_id integer
primary key PK (cafe_id, cafe_type_id)

Then you do a query on cafe-types like so: 

SELECT c.* 
FROM cafe c
INNER JOIN cafetype_link cl ON (c.id = cl.cafe_id)
INNER JOIN cafe_type ct ON (ct.id = cl.cafe_type_id)
WHERE cafe_type.name = 'irish pub'

This works very simple and very fast. However it is not as flexible as EAV.

Upvotes: 2

Related Questions