2

I have to design a database, but I don't yet know which underlying technology it will use (not just my decision). Could be SQL, could be NoSQL, could be something else.

I do have quite a few requirements and I have enough knowledge of the business domain to create a data model.

Obviously, not the physical model. I would like to create a technology independent logical model. First, I'd like to create an E-R diagram.

But I don't want to draw it via drag and drop. I would like to describe it in some language from which I could generate a diagram.

Does this exist?

I know E-R diagrams can be reverse-engineered from existing SQL databases, for example. But that's not what I want.

I am looking for a generalized technology-agnostic language for describing a logical data model. It would support defining objects, relations between them (one to many, many to many) and perhaps attributes and their types (in a general way, thus 'text' or 'string' and not varchar2(100)).

Basically an E-R diagram written out. After I create it, I'd commit it to Github and share and maintain that way. Diagrams are quite useful, so I should be able to import it to some tool to get a diagram from it.

Any ideas?

3

An entity-relationship model represent some kind of graph (the mathematical structure, not the visual presentation): there are nodes and edges that join pairs of nodes. Both nodes and edges have properties that describe the entity or the association.

So any language able to describe a graph could be used to express an ER model. For example:

  • XML. The advantage is that there are plenty of parsers around, so that you could focus on the mapping (hint: have a look at xml schemas).
  • similar endeavors exist that tried to have a textual description of UML models. For example plantuml .Of course you’d have to adapt to ER and enrich it.
  • simple logical predicates could also express the model: entity(x), entity(y), association(z,x,1,y,n), property(name,string,x)... (also easy to parse)
  • json, maybe on conjunction with graphql
|improve this answer|||||
  • 1
    I could be wrong but I think what the OP was asking for was a succinct language which has the expressive power of a diagram but which is capable of being written. I think mention of XML and other encodings are slightly at odds with this, because not only do they have a heavyweight and inhuman syntax, but they do not provide a vocabulary for dealing with the concepts which are particular to designing and describing the storage of data (whether in a relational database or otherwise). – Steve Feb 8 at 19:36
  • @Steve yes, you’re right. this is why i also made some other suggestions. If I’d try to do something nice and had plenty of resources I’d probably start either from the logical predicates (because it would also provide the basis for model querying using simple unification) or I’d invent an extension to sql to express not tables but entities and associations. But if I’d want to maximize my own efforts, I’d probably go for an appropriate xml scheme or json because a lot of libraries would take care of the parsing, and a lot of tools could process or query the models. – Christophe Feb 8 at 19:50
  • Thanks @Christophe. I like plantuml, if nothing else crops up I'll accept that answer. But I was hoping that there would exist a data schema definition (or data modelling) language that is similar to but one step up (more abstract) from SQL DDL (which is good, but has no relations). I think it would be useful for master data definitions in big organizations/projects which may have many implementations of same data in different systems (SQL, NoSQL...). It's strange that what I'm looking for can be drawn (E-R diagrams), but not written in a clear, accepted, standardized manner. – Poglavar Feb 10 at 0:34
  • Just to add, I don't really think the format itself is important. It could be yaml, json, even xml if must be, but I don't really wish to create my own standard. Sure, I could take SQL DDL and append to the bottom of each CREATE TABLE lines in the format HAS ZERO OR MORE OF <table X>. But looking for something that's established and being used, and can be understood by (loaded into) at least one diagramming tool. Also not too SQL-centric, if I end up creating a NoSQL database. – Poglavar Feb 10 at 0:52
1

I guess the most frequently used language for describing an ER model is SQL, specificially the DDL part of SQL. If you want to create diagrams from this kind of description, you either need to implement some kind of SQL parser, or alternatively run the DDLs against some empty DB instance and reverse engineer from there-

Of course, this is only technology-agnostic to the same degree standard SQL is, though you could simply introduce some "unspecific" data types which would lead to your own DDL variant based on SQL.

|improve this answer|||||
  • 1
    Indeed, several tools (e.g PowerDesigner) are able to reverse engineer SQL to ER either directly from database or the DDL. I did not mention SQL in my answer because, you cannot directly describe all of the ER relations in SQL (e.g a foreign key constraint does not have a name, a many to many relation is itself a table, etc...), you can only deduce them. – Christophe Feb 8 at 9:35
  • @Christophe, it made me think for a while about the nature of a many/many relationship in SQL. In truth, all relationships can be modelled as mapping tables which are external to the core tables - I would suggest the absence of this table (in typical one/one or one/many relationships), and the integration of the foreign key into either the "many" table in a one/many relationship, or typically into the "subordinate" table in a one-one relationship, is a kind of simplification/optimisation. – Steve Feb 8 at 19:47
  • 1
    @Steve yes but it would still be a deduction to go back to the ER model (I did such things for quite a long time for refactoring/optimizing databases). If you want to go this path you should think of an SQL like language on which you’d CREATE ENTITY and CREATE RELATION instead of CREATE TABLE. But this is a different approach: SQL is standardized and that’s its main benefit. Looking at growth of NoSQL there are plenty of needs that are not so well addressed by SQL. And reinventing a full new language will require lots of effort and find little acceptance on an overcrowded market ;-) – Christophe Feb 8 at 19:57
  • @Christophe, I agree. I would guess that SQL itself is probably the closest match to what the OP wanted, but as you say it lacks the ability to conveniently express a distinction between "entity" tables and "relation" tables, or columns which are present in a table because they form part of the fundamental attributes of the entity, and columns which are present because they form part of a foreign key which records the relationship between the entity and other tables. – Steve Feb 8 at 21:30
0

I have finally found what I need in these two tools, each with some drawbacks:

https://dbdiagram.io/d (no many to many) https://app.quickdatabasediagrams.com/ (only 10 tables in free tier)

The first tool is based on a language defined at https://www.dbml.org/home/

This does the job for me, and answers my question. I hope others find it useful too.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.