0

I have started working on designing a database in MariaDB in a company. This is first time I am working on it. ( Previously I used to work for Business Objects in Datawarehousing )

However I have created the tables and have joined them as per requirements.

I encounter loops, chasm traps and fan traps in the layout which I have created. Do I need to resolve them before working on the database ??

All the search I am doing in the internet about these problems fetches results which are about solving them in Universe Designer ( a tool in Business Objects ) using aliases and contexts. Hence I am confused if I need to resolve them for a database once designed or those are just to be solved if you are working on Universe Designer.

Please let me know how can they be solved in database if to be solved. Please provide some reference material if possible.

sravan kumar
  • 583
  • 3
  • 11
  • 24
  • That's a fairly broad question. In *general*, resolving those issues in the database is preferred, since it not only makes the universe development easier, but also supports non-BO queries. But that's not always feasible. That is, there may be a good reason for the datamart design, and you just have to work around it in the universe. If you can be more specific in your question, I may be able to offer better guidance. – Joe Aug 27 '14 at 16:57
  • What exactly do you mean that you "encounter" loops and traps? That there are certain arrows in a picture of your design? That the SQL you are writing is not returning the rows you want? What do mean by "resolving" them? – philipxy Aug 29 '14 at 00:37

1 Answers1

0

Chasm and fan "traps" are not properties of designs. They are ways of improperly using a database by blindly writing a (wrong) query based on certain properties of designs (namely constraints) rather than properly (namely the meanings of the tables). Having such properties in your design doesn't mean there's anything wrong. Of course, you still might have ill-chosen base tables.

Every base table has a corresponding predicate aka fill-in-the-[named-]blanks statement describing the application situation. The names of the blanks in the predicate are the columns of the table. A base table holds the rows that make a true statement. Every query expression combines base table names via JOIN, UNION, SELECT, EXCEPT, WHERE condition, etc and has a corresponding predicate that combines base table predicates via (respectively) AND, OR, EXISTS, AND NOT, AND condition, etc. A query result holds the rows that make a true statement.

A table and its predicate both represent an application relationship. (Aka relation. Hence the relational model, and entity-relationship modeling.) The table holds the rows that satisfy the predicate. You can't update or interpret the table without knowing the predicate. Many design methods and products do not make this all clear.

But the arrows/lines that get called "relationships" in (poor presentations of) ERM and ORM denote not application relationships but constraints. They are known limitations on what the database state can be from setting the base tables per the predicates. They have nothing to do with writing queries. (Although the limitations on base table values will naturally be reflected as limitations in query results.) So there is nothing to "resolve".

"Loops" aka cycles in these arrows/lines just reflect certain multiple-table constraints and there is nothing wrong with that per se. But arrows/lines corresponding to foreign key constraints get used by SQL DBMSs also for cascades since such a multiple-table constraint frequently requires a multiple-table update. It is cascades that must be acyclic. It just happens that some DBMSs restrict expression of constraints as a byproduct of restricting cascades. So you might have to "resolve" them to satisfy such a DBMS.

See this answer and this one.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83