Code to store AND, OR relation, association

Question

Many thanks for your help on this.

For the first example entry must have qualification 2 in sector 5.

Requirement can be anything (single, or multiple qualifications using AND, OR logical operators)

1 What is the nature of the relation between Notice and Requirement ?

• A Notice has one or more requirements, like in these four examples.

2 Between Tender and Requirement

• Tender has one or many Requirements.

3 Between Tender and Notice ?

• I have already told you that Tender, Notice are the same thing.
• Name it Entry or Example or whatever you want.

I just want to store these raw numbers, forget about class, names... You have 4 examples, which are raw numbers, you don't need anything else, because there is nothing else.

---

Example 1

• Sector/Qualification 5.2

---

Example 2

• Sector/Qualifications 3.9 and 4.10

---

Example 3

• Sector/Qualifications 6.1 or 6.3

---

Example 4

• Sector/Qualifications (3.1 and 3.2) or (5.6 and 7.1)

---

Example 5

• Sector/Qualifications (3.1 or 3.2) and (5.6 or 7.1)

---

Example 6

• Sector/Qualifications (6.4 or (3.7 and 1.1)) and (9.6 or 8.5)

---

class Example < ActiveRecord::Base
  has_many :requirements
end

class Requirement < ActiveRecord::Base
  belongs_to :example
end

---

  create_table "examples", :force => true do |t|
    t.integer "example_number"
  end

  create_table "requirements", :force => true do |t|
    t.integer "example_id"
    t.integer "sector_number"
    t.integer "qualification_number"
  end

Answer 1

The problem you are experiencing is, trying to force-fit your OO classes into a Relational database.

If you want the power and flexibility of a Relational databases; if you wish to avoid "re-factoring" the data every time you "re-factor" your classes, do not do that.

Forget about your classes, and model the data as data. It will not be "complex" or "tricky". When you are finished, map your classes to the data.

If you want more information on the absurdity of implementing OO classes in the database, read this answer starting from the 11 Dec 10 entry.

Now if you tell us what your data is, and how it is related to each other, we can model that for you.

Responses to Comments 27 Dec 10

Sure thing. Forget the tables and classes for now, just think about the data, in terms of Entities or Things, Nouns, that may relate to each other. Do not worry about the AND/OR, that is easy once the data is modelled correctly (and impossible if not !).

1) Does the Class identify the Qualification; Sector ? And that is independent of Enterprise ? (I think so, so I have modelled that.)
2) Do you have names instead of numbers for MinimumClass/Sector/Qualification ? (Please stop sticking Id columns on everything that moves: it seriously hinders the modelling process; understanding the data.) I need examples of real data: Sector; Qualification; Class.
3) Does each of Class, Sector, Qualification exist on its own; eg. Qualification exists only in the context of Sector ?

Have a look at this initial ER Diagram (Page 1) (we are not ready for the Data Model.) (inline links do not work on some browsers/versions.)

• You have a good idea of what you want; I am asking you to set that aside and think about just the data and how it relates to other data
• I suspect the model on the left is what you have been staring at; it is familiar; but there is too much going on in one place. It is definitely not Normalised, and that is what we have to fix first.
• I think you cannot have a Qualification without a Sector. Right ?
• next, a Qualification exists independent of Class
• next, a Class has a set of Requirements, which consists of Qualifications. Right ?
• then we can think about what a Class is; what those Requirements are; the ANDs and ORs.

1) The link provides Sector-Qualification numbers, sure, but that looks more like chapter-section numbers that will change next year. I accept that people do talk about a "Classe Une qualification"; but I doubt the people actually discuss a "1.3 qualification". I have modelled it based on what you stated, but if the Sector/Qualification numbers are not stable, you are better of either using increment of 10; or some other number or code that means something to you and not the outside world, The Name is the meaning that will not change.

2) AND/OR. I have used two methods in the past that are useful here. One was based on a series of BIT or BOOLEAN Indicators, such as IsOne and IsOneOfTwo; that was more useful for the coding involved; and there were IsMandatory requirements involved.

For you, a simpler method using a Weight column is all you need. The total Weight of any qualifying set for a Class, in the same Sector* (not the total Weight for a Class, they may be several sets) equals 1.0. If the Weight of a row for a Class, in the same Sector, is less than 1, you need to keep ANDing it with other rows, to make a full Class. This will allow you to have threes in future. I have provided the full set of rows for the examples you used. Ask specific questions if any of that is not completely understood.

Data Model

Evaluate this Initial Data Model (Page 2) (inline links do not work on some browsers/versions.)

Readers who are not familiar with the Relational Modelling Standard may find the IDEF1X Notation useful.

Response to Comments 01 Jan 11

3) If I have (3.1 and 3.2) or (5.6 and 5.9) if I use 0.5, how can I differentiate this with (3.1 and 3.2 and 5.6 and 5.9), I must use a different weight for each group that's right ? 0.5 for the (3.1 and 3.2), and 0.6 for (5.6 and 5.9) ?

No. You've added a bit to the question (no problem; modelling is a back-and-forth exercise). Never play with trying to identify control in the data. The control lies in the Keys, the Identifiers. That differentiation is already provided in the Model. The changed requirement (question, not table) is now:

• For a Class to Qualify, the weight of the Requirement must be at least 1.0 in the same Sector

(5.6 and 5.9) remain at 0.5 each; they are a different Sector (5) to (3.1 and 3.2) which are (3).

4) Have you read and understood the IDEF1X Notation, and do you appreciate the compound keys in the model ? Requirement PK is (ClassNo, SectorNo, QaulificationNo); the only data is Weight.

5) Are you clear about the Business Rules that the Model supports ? Would you like me to list them ?

Responses to Comments 02 Jan 11

Some precision, (1) Class is completely different from Sector/Qualification, they are not connected directly.

Yes, it already is independent. Class is as per all statements above, plus Sector and Qualification being correct: "SECTORS; ACTIVITY OF QUALIFICATION AND CLASSIFICATION OF THE COMPANIES ENGAGING IN THE BUILDING SECTOR". We have to take one step back, and confirm the simple rules before moving forward. New Data Model. Please confirm/deny each of these statements, working side-by-side with the DM:

• A Sector is made up of 1-to-many Qualifications (the link provided)

• A Qualification is a requirement in 1-to-many Classifications

• A Classification requires 1-to-many Qualifications:

  • a set of Qualifications is required to make a Qualification
  • Some Qualifications are OR-ed
  • Some Qualifications are AND-ed
  • the set of Qualifications required for a Classification, that are OR-ed or AND-ed, must be in the same Sector

• (New info) A Classification Notice is issued to 1-to-many Enterprises.

• An Enterprise is endorsed with 1-to-many Classifications.

(2) You assume that the two operands have the same Sector, it can be (3.1 and 4.6) or (5.6 and 1.9).

(I am assuming nothing, I am distilling the info provided by you: give me more info, and I will give you a more precise model) ;-}

No problem. (2.a) Identify the basis for (3.1 and 4.6) being grouped. (2.b) Identify the name of each Classification (3.1 and 4.6), (5.6 and 1.9); what makes them different/distinct from the group (3.1 and 3.2)

(4) for the ERD and dm we just need to change Enterprise to Notice and connect it to Requirement.

Don't worry about the "connections", that is the job I have accepted, at your request. ;-}

(5) each Notice has many Requirements that each enterprise must meet to be allowed to participate.

(5.a) What is the content of these Notices; is it not an endorsement that they have achieved a Classification ? Or that they have achieved a single Requirement ?

(5.b) Does the system have to track each requirement that each Enterprise meets ?

(5.c) Separate to requirements, when an Enterprise achieves a Classification (set of Requirements) does the system issue a Notice of Classification ?

Update

Based on the new information, I can see where you are heading. Please evaluate this Very Tentative Data Model (Page 3).

• The OR-ing and AND-ing is even simpler
• Each Classification has a set of possible Requirements (OR-ed)
• Each ClassificationSet is required in full (AND-ed)
• Weight is no longer required
• This tracks each Qualification awarded to an Enterprise (user inserts this); and updates IsNotified when the Notice is issued
• and each Classification achieved

Perhaps daily, a procedure is required to process the EnterpriseQualifications (compare against a ClassificationSet) and produce (insert) EnterpriseClassifications. And again update IsNotified when the Notice is issued.

Ok, I have removed the bits you don't need, as per comments, and updated the Tentative Data Model.

Awaiting response re the remaining questions. So now it is an Incomplete Data Model (Page 4).

In order to "connect" Notice to Requirements, I need to know the basis of the relation; it cannot be one Notice to many, any Requirement.

Simplicity. Actually, I am a master of simplicity. K.I.S.S. isn't a shallow mantra, there is actually a science to it. In order to eliminate problems during development and coding, you need a clean data model; simple, uncomplicated. That's what I am trying to give you. But I need information.

Awaiting response re the remaining questions. So now it is an Confused Data Model (Page 5).

Response to Comments of 05 Jan 11

Ok, we are getting somewhere. I have revised the DM based on your latest revision of the question. We now have the Fifth Data Model (Page 6) (not a problem, that is the nature of modelling, paper is cheap; re-factoring is expensive).

• The initially identified, Class, etc, are removed. Notice is Tender.

• Names, identification, definition, are actually very important, you will realise that once you start coding; and later when you need to expand the Data Model.

• The Weight that was based on the earlier understanding, is also removed. We now have the notion of optional sets of Requirements per Tender (the OR), and each set is mandatory (the AND).

• the code, and the class definitions, will now be dead simple.

• I have used the exact data values provided in your latest examples.

• Blue denotes static Reference tables; Green denotes Identifying elements.

• Please ensure that you read the IDEF1X Notation document, so that you can fully interpret the DM, and produce DDL. That will have the full integrity, strength and power (flexibility) of the Relational Model.

When this is complete, I will clean up my answer, delete all the obsolete information that we worked through.

Response to Comments of 07 Jan 11

(1) The examples 2 and 3 are stored in the same way, how do you distinguish (3) OR from (2) AND ?

Data specification error on my side. Fifth Data Model (Page 7) unchanged, with corrected data.

(2) Does it supports any combination of OR/AND requirements ? These are just examples it can be anything, like (6.4 or (3.7 and 1.1)) and (9.6 or 8.5). It must be flexible.

The Data Model has all the "flexibility" that you had previously identified. No, it does not support any requirement that you have not identified (I have a fair amount of capability but ESP is something I do not have!).

In that case, evidently, you do not "know exactly what you want to do", you are introducing new requirements, again. And the data is not raw, it is very specific control data, so please, let's stop treating this as a simple requirement to "store raw data", and get on with the not-so-simple job.

What other "flexibility", "exactly", do you require ? Specify all those forms, and I will give you a Data Model that satisfies all the requirements; that will take less time and effort than this incremental back-and-forth method you are using.

• In case you have forgotten, here is an Early version of your Question. All the revisions of your question are available for anyone with enough points to see.

• The incremental method is no doubt good for you, to understand your data, and to understand the progression of the model, but as you can see it is a very slow and time-consuming road. Fine when you are working alone and have all the time in the world; not fine when you are working with a professional.

• The fast road is to identify all your requirements up front, and let the professional deliver a complete data model. (Eg. your latest new requirement will take me five minutes; but I could have included that in the last Data Model if you had identified it; and of course that would not be complete either.) Then ask questions to fill in any gaps of understanding.

• An experience professional Data Modeller does not need to work in tiny incremental steps. We look at the whole picture (we do want a Relational Database that has no duplication) and normalise the entire model. Once.

• And do not waste time defining your objects and classes either, they will keep changing with every tiny increment; you can wait until the Data Model is complete, and define your classes, once. That will be stable.

I have updated the document to show the series of revisions that have been produced for the Question as a result of your demands for small incremental progress.

I have annotated Page 7 with the above, but the Fifth Data Model remains unchanged.

Please make all amendments (specify all the "exact" "flexibility" you require) to your question, and I will answer it. Please do not change the text of your Question or delete anything; leave what is there alone, and add to it.

Response to Comments of 08 Jan 11

(1) I added two more examples, I'll be pleased to see your generic method, I want it. Anyway with these two examples I think it's enough for most cases.

Those new examples introduce (implicitly) yet more new requirements, that of:

• basically a tree structure (unless you want massive duplication and the consequent data integrity problem).

• AND and OR occurring at any point/node in the tree

  • That is supplied by a Boolean IsAndSet; false means it is an OR set.
    .

• a RequirementSet can be made up of:

  • Qualifications;
  • other RequirementSets (unless you want massive duplication);

  • or both

  • That requires a Subtype (explained in the IDEF1X Notation document) to allow for the optional columns, the Foreign Key. There is only one, therefore no Discriminator is required. It is an Exclusive Supertype/Subtype.

  • It remains optional, only for a RequirementSet that is made up of other RequirementSets

• The Nodes are Atomic or Leaf Level, anything else is not (it is made up of Nodes). The Nodes eliminate duplicate data and Update Anomalies.

Some developers would collapse the RequirementSet and RequirementSetNode into one table: that will be de-normalised and difficult to maintain. Further, it would require Null Foreign Keys. Each of those separately break Relational Modelling and design rules, and have consequential problems, so I do not provide that, I provide only Standard-compliant models. It may look like it is harder to code for but actually it is easier.

I have provided all the data to supply your examples (5) and (6), as well as a tree diagram. You can refactor the data supplied for examples (1) to (4) [if you had given these examples at the outset, the five previous versions of the Data Model would not have been required, and the data I supplied for them would not need refactoring].

Updated the Data Model, we are now on at Sixth Data Model (Page 8)

Next example ... ?

Response to Comments of 10 Jan 11

(1) I don't see why it can't store any combination.

It can, AFAIK ... but there is always the possibility that someone in New Caledonia comes up with something it can't store; being conservative, I do not declare that it can store any combination; just that it is generic and extremely flexible, while maintaining full Relational integrity and power.

(2) Can you please explain what do you mean by "The Nodes are Atomic or Leaf Level" ? You mean that it doesn't include the head of the tree ?

Those terms are common in B-Tree and Directory structures.

I have changed RequirementSetNode to RequirementSetChild to improve understanding.

• Leaf level is the very end of any branch. Note that the Leaves are not all at the same Level. In our case, they are always Requirements.

• A Node is any branch or joint in the tree, in the tree diagram (not the model); a collection of either Nodes or Leaves or a combination. In our case, they are RequirementSets.

  • The collection of Leaves belonging to a RequirementSet is in Requirement
  • The collection of Nodes belonging to a RequirementSet is in RequirementSetChild.
    .

• Atomic is a Transaction (IEC/ISO/ANSI SQL Database) term, from the ACID Properties. Atomic means it is indivisible; either the whole unit is valid; or it is invalid. What I am stating here is, from your app point of view, you just need to treat each Node (the items it contains) together, as one unit.

• (The Leaf is always Atomic.)

The exact data examples should clarify; I have provided an expansion on page 9.

(3) "It remains optional, only for a RequirementSet that is made up of other RequirementSets" What does remain optional ?

RequirementSetChild is an optional child of RequirementSet. Not every RequirementSet has RequirementSetChildren; only the RequirementSet that are Nodes, hence it is optional. (But of course, the child always has a parent; that is a Foreign Key, and not optional.)

(4) "It is an Exclusive Supertype/Subtype." What is the Supertype and Subtype in your model? What does Exclusive mean in this case, a Subtype can't have Subtypes?

Actually, since there is only one Subtype, we can remove the whole Supertype::Subtype issue, and treat it as a simple optional child. Model Changed.

Answer 2

If you follow the 'test-driven' approach, you should start with some tests and write code to make them pass. So your most basic example, could start out as the following in RSpec. From what you've said you have a 'tender' and an 'enterprise' - I'd start there.

describe 'inviting an enterprise to tender' do

  describe 'for a tender that has a minimum class' do

    before do
       @tender = Tender.create(:minimum_class_level=>4)
    end

    it 'should invite the enterprise to tender if the enterprise has class level 4' do
       enterprise = Enterprise.create(:class_level=>4)
       enterprise.should be_invited_to_tender(@tender)
    end

    it 'should not invite the enterprise to tender if the enterprise has class level 3' do
       enterprise = Enterprise.create(:class_level=>3)
       enterprise.should_not be_invited_to_tender(@tender)
    end

end

Write some code to make that pass, and once that's green, move on to adding further models as and when required. Your data model will emerge, as will your code 'documentation' in the form of a test suite.

Answer 3

What you're building sounds terribly complex. But if you want to continue, you might want to look at has_many :something, :through => :something_else: http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-has_many