eav

Designing an 'Order' schema in which there are disparate product definition tables

This is a scenario I've seen in multiple places over the years; I'm wondering if anyone else has run across a better solution than I have... My company sells a relatively small number of products, however the products we sell are highly specialized (i.e. in order to select a given product, a significant number of details must be provide...

what's the best implemention of client creatable and modifiable web forms in a relational database?

In several web application projects I've been a part of, the client asks to be able to create their own forms. The question arises on how to store their form definitions, and then how to store user inputted values into those custom forms. I've seen it done two ways: Assuming that the client only defines how many fields, and what label...

Performance of large EAV/open schema systems on SQL Server

Has anyone implemented a very large EAV or open schema style database in SQL Server? I'm wondering if there are performance issues with this and how you were able to overcome those obstacles. ...

What is best practice for this problem (different properties for different categories)?

Hi. I have some products that belongs to the some category. Each category can have different properties. For example, category cars has properties color, power, ... category pets have properties weight, age, ... Number of categories is about 10-15. Number of properties in each category is 3-15. Number of products is very big. M...

Is there a Entity Attribute Value (EAV) framework out there for PHP/MySQL?

Is there a Entity Attribute Value framework out there for PHP/MySQL? I'm starting to write my own, but I feel like its been done already. Any suggestions? ...

How to define structure in a tag-based organization?

[former title: Is there a way to force a relationship structure on a tag-based organizational methodology?] I have some entities, and they have a series of attributes. Some of the attributes affect what other attributes the entity can have, many of the attributes are organized into groups, and occasionally entities are requited to have ...

Name Value pairs and fact tables

I'm working on a star schema for analysis of posted form data. The site that the form data will be posted to is actually external to the site hosting the form, so only the data in the form will be available. I'm going to give the option to include some extra useful information with hidden fields, original referrer, session id etc. I'll ...

datatype supplied by user for SQL field

I have a SQL table which has a number of fields ID | Value | Type A typical record may be :- 1000,10,[int] a second row may be:- 1001,foo,[string] a third row may be:- 1002,10/12/2008,[DateTime] I have been asked to look at this as at the moment, each time we wish to select from this table we have to cast the value to the type spe...

Questions about DB modelling...

How would you model these relationships in a db? You have a Page entity that can contain PageElements. A PageElement can for instance be an Article, or a Picture. An Article table obviously has other members / columns than a Picture. An article could have ie. "Title", "Lead", "Body" columns that are all of type nvarchar, while a Pictur...

Optimal DB structure for additional fields entity

I have a table in a DB (Postgres based), which acts like a superclass in object-oriented programming. It has a column 'type' which determines, which additional columns should be present in the table (sub-class properties). But I don't want the table to include all possible columns (all properties of all possible types). So I decided to ...

Creating a Metatable table in a database

Does anyone have any suggestions for creating meta tables in a database? These tables would be used to emulate tables in a database, using a database. This is for when you want to easily add structure (more fields) to a database on the fly without having to worry about all the technicalities involved. The only example I have looks someth...

SQL database problems with addressbook table design

Hello! I am writing a addressbook module for my software right now. I have the database set up so far that it supports a very flexible address-book configuration. I can create n-entries for every type I want. Type means here data like 'email', 'address', 'telephone' etc. I have a table named 'contact_profiles'. This only has two colu...

How to design a database schema to support tagging with categories?

I am trying to so something like Database Design for Tagging, except each of my tags are grouped into categories. For example, let's say I have a database about vehicles. Let's say we actually don't know very much about vehicles, so we can't specify the columns all vehicles will have. Therefore we shall "tag" vehicles with information...

Choosing a method to store user profiles?

I'm in the process of working on a user profile system for a website and am pondering what would be a better (scalable) approach to take. I've come up with two solutions and am looking for either input or perhaps pointers to something I might have missed. The following create table statements are not meant to be executable but are merel...

Table Module vs. Domain Model

I asked about Choosing a method to store user profiles the other day and received an interesting response from David Thomas Garcia suggesting I use the Table Module design pattern. It looks like this is probably the direction I want to take. Everything I've turned up with Google seems to be fairly high level discussion, so if anyone coul...

DataModel for Workflow/Business Process Application

What should be the data model for a work flow application? Currently we are using an Entity Attribute Value based model in SQL Server 2000 with the user having the ability to create dynamic forms (on asp.net), but as the data grows performance is getting down and hard to generate report and worse if too many users concurrently query the ...

How to design a database for unkown amount of 'meta'-data

I want to store certain items in the database with variable amount of properties. For example: An item can have 'url' and 'pdf' property both others do not en instead have 'image' and 'location' properties. So the problem is an some items can have some properties and others a lot. How would you design this database. How to make it se...

How do I search a "Property Bag" table in SQL?

I have a basic "property bag" table that stores attributes about my primary table "Card." So when I want to start doing some advanced searching for cards, I can do something like this: SELECT dbo.Card.Id, dbo.Card.Name FROM dbo.Card INNER JOIN dbo.CardProperty ON dbo.CardProperty.IdCrd = dbo.Card.Id WHERE dbo.CardProperty.Id...

Best beginner resources for understanding the EAV database model?

I'm doing some custom work with Magento, which has a database design based upon the EAV model, and I need some good resources/tutorials that explain the EAV database model. Please don't provide Wikipedia as a resource... I'm looking for something that teaches EAV in a friendly way for beginners. ...

Key/Value pairs in a database table

I need to design a Key/value table in my database and I'm looking for guidance on the best way to do this. Basically, I need to be able to associate values to a dynamic set of named properties and apply them to an external key. The operations I need to be able to support are: Apply a key/value pair to a group of items Enumerate all ...