Many-to-many relationship with foreign keys and arrays possible?

[simnh]

Hey,

I wondered if there is a way to represent a many to many (many to 1, 1 to many) relation within a data package using foreign-keys. I do not what to add another table representing this relation as it would be done in a relation database)

I have a datapackage.json example like the one below, where I would like to have a array of strings in one field and reference to another file the this foreign-key array.

{
  "profile": "tabular-data-package",
  "name": "test_data_set",
  "resources": [
    {
      "name": "hubs",
      "path": "elements/hubs.csv",
      "profile": "tabular-data-resource",
      "schema": {
        "fields": [
          {
            "name": "name",
            "type": "string"
          }
        ]
      }
    },
    {
      "name": "sinks",
      "profile": "tabular-data-resource",
      "path": "elements/sinks.csv",
      "schema": {
        "fields": [
          {
            "name": "name",
            "type": "string"
          }
          {
            "name": "predecessors_fkey",
            "type": "array"
          }
        ],
        "foreignKeys": [
          {
            "fields": "predecessors_fkey",
            "reference": {
              "resource": "hubs",
              "fields": "name"
            }
          }
        ]
      }
    }
  ]
}

[rufuspollock]

@simnh hmmm this is not really supported in Data Package specs at present. The normal way would be the m2m table. What's the objection to having the m2m table? (Having an array of keys seems a bit hacky though i can imagine it making sense with e.g. a tags field or similar ...).

[rufuspollock]

I think this is a good one for wontfix / add to FAQs. I've added the FAQs label and ready for PR.