-2

I'd like to create a clothing database, where items from tables such as 'tops' or 'bottoms' (pants, skirts) can be matched with each other and with items in other tables such as hats or shoes.

I want to be able to relate all tables to each other in both directions (each skirt goes with many tops, and each top goes with many pants, etc) and I want each table to be related to each other. E.g, I want to query 'what items look good with these pants' and see a list of shirts, shoes, etc.

Using a relational database would requires in-between tables for every relationship, which creates a ridiculous amount of tables.

I thought I might be able to do this with a NoSQL databases, but as I'm learning what those are, they don't seem to fit. Am I wrong?

Are there bi-directional relational databases, and what are they called?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Alice Greene
  • 21
  • 2
  • 1
    *"Using a relational database would requires in-between tables for every relationship, which creates a ridiculous amount of tables."* That's just how it works. If you think you're on to a new paradigm of database that hasn't been implemented before that can be implemented in a scalable, performant way, I say knock yourself out. – Jonathon Reinhart Oct 17 '16 at 22:17
  • As perhaps a churl, thinking of churlish things, I notice the -3 (although there are no votes to close) & wonder how you can improve this question. Maybe people think that you can't have done any "research" (see the upvote/downvote mouseover blurbs) because *any* reading about information modeling sufficient to understand an example (like your own) would have *quickly* disabused & informed you. I gave you the benefit of the doubt because I think the ubiquitous & persistent misconception that relational is directional bears dispelling. Can you edit your question to be more in line with [ask]? – philipxy Jan 23 '17 at 23:00
  • PS Personal touches are deprecated in SO. A bit of connecting a question's design/code to its application domain is ok. But though inappropriate & likely to be edited out, "thanks" & other technically irrelevant content like your intro wouldn't usually merit/evoke downvotes. I have to wonder whether "girl" got sexist downvotes where (say) "fashionista" wouldn't. But why mention gender? Or is it sexist of me to privilege gender among technical non-sequiturs? – philipxy Jan 24 '17 at 01:06

2 Answers2

0

Are there bi-directional relational databases, and what are they called?

They are called "relational databases".

Querying in relational databases has no directionality. Each table (base table or query result) in a relational database represents an application relationship of interest. A table holds the rows of values that are related in that way. (When subrows of values identify application entities, the relationship is about both values and application entities.) A base table is set to the rows of values that satisfy its associated application relationship. A query returns the rows of values that satisfy its application relationship that has been expressed in terms of the base table relationships.

Given that each base table represents a necessary application relationship, one defines sufficient base tables to describe the overall application state. To say that the number of tables is ridiculous is ridiculous. The decomposition of certain tables/relationships into others as suggested by normalization not only makes that description simpler but reduces redundancy and complications in manipulation.

The foreign key constraints in a relational database can reasonably be called directed. They tell the DBMS that the values under some columns in one place have to appear as values under some columns elsewhere. In ER (Entity-Relationship) modeling these are diagrammed as lines from entities and identifying relationships to the relationships they participate in. (The latter are your "in-between tables".) But which foreign key constraints hold has no effect on query composition.

You need to read an introduction to relational databases & DBMSs. (Avoid presentations based on ER modeling and ORM (Object-Relational Mapping) approaches, which do not really understand the relational model.) (NoSQL approaches don't either.)

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Thanks so much! Is there an introduction that you'd recommend? – Alice Greene Jan 19 '17 at 23:32
  • See free online [An Introduction to Relational Database Theory](http://bookboon.com/en/an-introduction-to-relational-database-theory-ebook) and its author's companion books giving SQL & exercises. Best information modeling approach is Halpin's on Object-Role Modeling (eg book Object-Role Modeling Fundamentals) but many books (best are textbooks), slides & courses (eg [Stanford](https://lagunita.stanford.edu/courses/DB/2014/SelfPaced/about)) are online . – philipxy Jan 20 '17 at 03:44
-1

They are called Object-Relational databases This is an excellent question, according to my experience, in ERD diagrams and relational databases direction is implied. In RDBMS you always define Many-To->One (trivial case One-To->One) relationships. The Many side of the relationship, a.k.a children, references the One side, a.k.a parent and you implement this with a Foreign Key constraint. Technically speaking you have to access an index, fetch the Primary Key record of the One side and then visit this record to get more information.

You cannot do this the other way around unless we are speaking about Object-Relational DBMS such as Postgres, Intersystems Cache, etc. These DBMS allow you to define a bi-directional relationship between the two entities (tables). In that case accessing records the other way around, i.e. One--To-->Many is achieved by using an array of references (children). In Object Relational Mapping programming modules-libraries you have classes that reference each other the same way we described here.

WARNING: Most RDBMS in the IT market are NOT relational database management systems in the strict sense, think about null values, duplicate records etc, many of these allowed features break the definition of what a Relation is. On the other hand YOU MAY HAVE BIDIRECTIONAL RELATIONAL DATABASES (and RDBMS). Relational theory perfectly allows any attribute to be of any type including array types of any kind. But in practice we observe several limitations because of the specific implementation of the DBMS by the vendor. See comments for a discussion about different types of DBMS.

Athanassios
  • 206
  • 1
  • 10
  • 1
    *You cannot do this the other way around* is **false**: in standard SQL, ie. genuine RDBMS, you can `SELECT WHERE = '. – PerformanceDBA May 06 '21 at 06:25
  • When you perform this query you write in your comment internally the DBMS has to follow the procedure I tried to describe in brief technically. "You cannot do this the other way around" is NOT referring to the user's (SQL client) level. I am talking about the core engine of the DBMS at a deep level where you do not have pointers in both sides. I will give you a vendor example, Intersystems Cache object relational database. Underneath they are using multidimensional arrays that are stored physically and with them they implement both sides of the relationship for their classes (tables). – Athanassios May 07 '21 at 13:42
  • I had a similar argument with RDF (semantic web) people at LinkedIn where I was trying to explain that triplets are unidirectional by nature. And they remain as such when someone implements that standard on RDBMS. On the contrary in property graph databases such as OrientDB the edge that connects two records is bidirectional, that is how it is implemented underneath. It is unfortunate that most people were taught to think about relationships only at semantic level (SQL) without understanding how records are connected at the physical level. – Athanassios May 07 '21 at 14:13
  • 1
    1) Your declaration was about RDBMS, which is false. My comment is about RDBMS.  2) I have no comment on other DBMS.  3) It works that way in the Client (semantic SQL request as above), and in the engine (processing); and in the storage (as per the data model). The *Relational Model* is semantic, thus SQL is semantic. The idea is to abstract oneself away from the physical: you are reversing 50 years of progress. Only the modeller (not the developer, not the client) needs to concern themselves with the physical; the storage; the indices; etc. – PerformanceDBA May 08 '21 at 22:57
  • 1
    4) In fact, the query is so basic; so fundamental, that it has been heavily optimised over the five decades, and it is stable.  a) only the table needs to be read  b) if in the is indexed, only an index scan (not [a] ) is necessary.  (This is for commercail RDBMS, not the freeware, which is a sad joke, 40 years behind.) – PerformanceDBA May 08 '21 at 23:01
  • @PerformanceDBA there are many dogmatic fights in the web about which system can be characterised as a true RDBMS and which not. It is pointless to start one with you. But YOU MAY HAVE BIDIRECTIONAL RELATIONAL DATABASES (and RDBMS) Relational theory perfectly allows any attribute to be of any type including array types of any kind. In practice we observe several limitation on the implementation of the DBMS by the vendor. – Athanassios May 11 '21 at 07:03
  • 1
    There is no dogma in science (there is in nonsense). Read the *[Relational Model](https://www.softwaregems.com.au/Documents/Article/Database/Relational%20Model/Codd%20E%20F/A%20Relational%20Model%20of%20Data%20for%20Large%20Shared%20Data%20Banks.pdf)* for yourself, just ch 1. There is no such thing as a bidirectional Relational database, the notion is hysterical, like calling a pipe a bidirectional channel. I am correcting you from science, not dogma. To a person who has just seen a relational database for the first time, all relationships in a relational database are "bidirectional". Ok. – PerformanceDBA May 12 '21 at 14:23