-1

I love the flexible schema capabilities of CouchDB and MongoDB, but I also love the relational 'join' capability of SQL Server. What I really want is the ability to have tables such as PERSON, COMPANY and ORDER that are basically 'open-schema' where each table has an ID but the rest of the columns are defined json-style {ID:12,firstname:"Pete",surname:"smith",height:"180"}, but where I can efficiently join PERSON to COMPANY either directly or via a many-to-many xref table. Does anyone know if SQL Server has any plans to incorporate 'open schema' in SQL, or whether Mongo or Couch have plans to support efficient joining? Thanks very much.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Journeyman
  • 10,011
  • 16
  • 81
  • 129
  • 1
    IHMO you should try SPARSE COLUMNS – Manfred Sorg Mar 31 '11 at 12:29
  • See: [Five Simple Database Design Errors You Should Avoid](http://www.simple-talk.com/sql/database-administration/five-simple--database-design-errors-you-should-avoid/) - EAV are bad design, avoid them - so I'm glad SQL Server doesn't support them! – marc_s Apr 01 '11 at 06:20

4 Answers4

2

CouchDB offers a number of ways to establish relationships between your various documents/entities. Check out this article on the wiki to get started.

The tendency, when coming from a relational background, is to continue using the same terminology and mindset whenever you try to solve problems. It's very important to understand that NoSQL solutions are very different, otherwise they have no real purpose for existing. You should really seek to understand how these various NoSQL solutions work so you can compare them with your application's requirements to see if it's an appropriate fit.

Dominic Barnes
  • 28,083
  • 8
  • 65
  • 90
1

MongoDB = NoSQL = No Joins - never ever.

If you need JOINs due to your data model or project requirements: stay with a RDBMS.

Alternatives in MongoDB:

  • denormalization

  • using embedded documents

  • multiple queries

  • So what would you recommend I do if I need joins, but I also need a flexible schema for my main tables? – Journeyman Mar 31 '11 at 12:46
  • You have not read my answer and did not reflect about your options using MongoDB. –  Mar 31 '11 at 13:17
1

As much as this would be inefficient to Query on a large scale, from a technical standpoint, using the XML datatype would allow you to store whatever structure you wanted that can vary by row.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
0

Not that I'm aware of, but it's not that hard to role your own EAV, it's only 3 tables after all :)

  • Entity stores the associated table name.
  • Attribute stores the column name, data type and whether it's nullable.
  • Value contains one nullable column for each required data type.

Entity 1..* Attribute 1..* Values

Assuming you're using .NET, define your EAV interfaces, create some POCO's and let Entity Framework or your ORM of choice wire up the associations for you. LINQ works great for this sort of operation.

It also allows you to create a hybrid model, where parts of the schema are known but you still want flexibility for custom data. If you design your domain model with this in mind (i.e. use the EAV interfaces in your model) the EAV can be baked in to the EF data context (or whatever) to automate the loading of attributes and their values for each entity. Your EF entity just needs to know which table entity it belongs to.

Of course it's not the perfect solution, as you're (potentially) trading performance for flexibility. Depending on the amount of data you want to persist and the performance requirements, it may be more suited to models where most of the schema is known and a smaller percentage is unknown. YMMV.

si618
  • 16,580
  • 12
  • 67
  • 84