eav

Hybrid EAV/CR model via WCF (and statically-typed language)?

Background I'm working on the architecture for a cloud-based LOB application, using Silverlight for the client, WCF, ASP.NET/C# for server and SQL Server for storage. The data model requires some flexibility per user (ability to add custom properties and define validation rules for them, for example), and a hybrid EAV/CR persistence mod...

Simulating variable column names in sqlite

I want to store entries (a set of key=>value pairs) in a database, but the keys vary from entry to entry. I thought of storing with two tables, (1) of the keys for each entry and (2) of the values of specific keys for each entry, where entries share a common id field in both tables, but I am not sure how to pull entries as a key=>value ...

SQL - how to select all related rows with external (?) conditions

Hi. I have three tables: player [id, name] attribute [id, name] player_attribute [id, player_id, attribute_id, value] each player can have different attributes, some of them don't have any. Now I need to search for players with certain attributes, e.g. all players that have number 11, and their first name is John. At this moment I al...

SQL SELECT criteria in another table

I have 2 related tables: messages -------- mid subject --- ----------------- 1 Hello world 2 Bye world 3 The third message 4 Last one properties ---------- pid mid name value --- --- ---------------- ----------- 1 1 read false 2 1 importance high 3 2 read false 4 2 i...

MYSQL query help (EAV Table)

I have the following query to retrieve customers who answer YES to a particular question "OR" NO to another question. SELECT customers.id FROM customers, responses WHERE ( ( responses.question_id = 5 AND responses.value_enum = 'YES' ) OR ( responses.question_id = 9 AND responses.value_enum = 'NO' ) ) GROUP BY customers.id Which works ...

Oracle: How do I grab a default value when a more specific value is null from within the same query?

That title is brutal, but I don't know how else to put it. I have a key value table tied to a user_id that stores user preferences throughout the site. If a user hasn't gone in and updated any of their settings, any time I ask for a key (say "FAVORITE_COLOR" it's going to be null, so I need to pull the default setting that I have tied ...

Loop in MySql, or alternative?

I have a MySql db with innoDB tables. Very simplified this is how two tables are layed out: Table A: controlID(PK) controlText Table B: controlOptionID(pk) controlID(FK to table A) controlOptionType controlOptionValue So many controlOptions(table B) can reference one control(giving that control m...

Customizable user's addresses implementation

Hi, I am working on a billing system for service provider and I have to implement something named Customizable preferences schema. Main feature should be to allow administrator to define which fields are mandatory in various situations. E.g. if customer only registers on site, he has to enter first name, last name and e-mail address. If...

How can I combine the awesomness of SQLAlchemy and EAV DB schemas?

Aight ya'll, I gotta lay a Python question on you... I've been doing some work with Pylons recently and quite like the SQLAlchemy model for database interaction. There's one section of my website though which I think could benefit from an EAV schema. Using this as my table example: id | userid | type | value ---+--------+--------|--...

Design of an 'EAV' or 'Class/Concrete Table Inheritance' database for stock control

I am developing a stock control system for a construction project. The storeman is responsible for adding new stock and distributing/returning it to/from employees. The items (and hence their attributes) will be very varied; e.g. steelwork, clothing, plant/machinery, tools, etc. My question is whether to go for a Class/Concrete Table ...

MySQL self-join for an EAV based stock control application

This question relates to the schema I suggested in my original question regarding a stock control application. I'm trying to create a MySQL query that provides the current stock for a particular item. The query is working but I wondered whether there is a more efficient way of obtaining the information I require. SELECT 's'.*, '...

Keeping page changes history. A bit like SO does for revisions.

I have a CMS system that stores data across tables like this: Entries Table +----+-------+------+--------+--------+ | id | title | text | index1 | index2 | +----+-------+------+--------+--------+ Entries META Table +----+----------+-------+-------+ | id | entry_id | value | param | +----+----------+-------+-------+ Files Table +----+-...

Records with extra properties: sparse table or EAV?

I have a model that already has a couple dozen of columns that will be filled most of the time. Now I need to add fields that might be different each time. what's the best approach? I don't like the EAV pattern. I don't like the idea of having a sparse table either, especially considering how these extra properties could be very differe...

Create a summary result with one query.

I have a table with the following format. mysql> describe unit_characteristics; +----------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+------------------+------+-----+---------+----------------+ | id ...

Normalize SQL table

Can we furthur Normalize this Sql Table Structue is Primary key is ID,Date,NAme,Type ID Date Name Type Value ----------------------------------------------------- 1 08/15/2010 Rating A+ 10 1 08/15/2010 Rating A- 10 1 08/15/2010 ...

Transact-SQL Query / How to combine multiple JOIN statements?

I'm banging my head on this SQL puzzle since a couple of hours already, so i thought to myself : "Hey, why don't you ask the Stack and allow the web to benefit from the solution?" So here it is. First thing, these are my SQL tables: Fields FieldID INT (PK) FieldName NVARCHAR(50) (IX) FormFields FieldID INT (FK) FormID INT (FK) Va...

magento select attribute by using where condition

$attributeInfo = Mage::getResourceModel('eav/entity_attribute_collection') ->setCodeFilter('modellijn') ->addAttributeToFilter('brand', 114) ->getFirstItem(); Hi there. I would like to know the right syntax for the following: Select the attribute modellijn where the attribute brand=114. The above syntax gives back an error. I ha...

Examples of EAV pattern in .NET

I've been reviewing the architecture of Magento Commerce today and am intrigued by their use of EAV to create a very flexible model. Are there any examples of this in the .net space? I had previously looked into storing additional attributes in a dynamic dictionary and serializing these to an object store. This way I would be able to ...

How to map EF4 Complex Type as seperate/dedicated table in Database.

Is it possible to use EF4 ComplexType as Table (similar to EAV style)? From the designer, I define ComplexType X as part of Entity A. In Database, I would like to have Table A and X, where table A has a foreign key to Table X. ...

saving and retrieving from EAV table structure using polymorphism

I have an attribute table like the following Attributes attributeid backendtype code displayname 1 int size Product Size 2 text description Product Description i then have the product attribute tables ProductAttributes_ints id attributeid productid ...