Comment puis-je traiter différents types de données dans un modèle entité-attribut-valeur (par exemple, un tableau unique avec plusieurs colonnes ou plusieurs tableaux par type de données)?

je veux créer une table de métadonnées patient/échantillon en utilisant une approche entité-attribut-valeur (EAV).

Question: comment gérer le type de colonne Variable du valeur (par exemple chaîne, numérique, ou clé étrangère à la table du dictionnaire) basé sur le l'attribut?

Remarque: je ne demande pas de savoir si ou de ne pas utiliser une VAE approche. J'ai regardé autres questions SO et références et croyez que c'est la meilleure approche pour mon cas d'utilisation (par exemple, Je ne veux pas créer une colonne ou une table séparée pour chaque l'attribut - qui peut se comptent par centaines). Cependant, je vais certainement reconsidérer d'autres conceptions donné un exemple complet.

Données Représentatives

un patient / échantillon ( entity) peut avoir plusieurs métadonnées attributs (p. ex. emplacement du laboratoire, survie, type de tumeur) chacun avec un autre valeur type (par exemple,VARCHAR,NUMBER,FOREIGN_KEY*, respectivement).

*FOREIGN_KEY signifie que ce valeur type est une clé étrangère ID (INTEGER) à une table de dictionnaire devaleurs (par exemple, une liste des 10 types de tumeurs). Donc l'emplacement du laboratoire peut être VARCHAR car je ne me soucie pas de normaliser ces valeurs. Mais le type de tumeur devrait avoir un certain degré de validation.

ma disposition de table peut ressembler à quelque chose comme ceci:

CREATE TABLE patients (
  patient_id INTEGER CONSTRAINT pk_patients PRIMARY KEY,
  patient_name VARCHAR2(50) NOT NULL
);

CREATE TABLE metadata_attributes (
  attribute_id INTEGER CONSTRAINT pk_metadata_attributes PRIMARY KEY,
  attribute_name VARCHAR2(50) NOT NULL,
  attribute_value_type VARCHAR(50) NOT NULL -- e.g. VARCHAR, NUMBER, or ID
);

CREATE TABLE patient_metadata (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value ???
);

je crois avoir besoin d'un valeur tapez colonne d'identification (attribut_value_type) dans la table metadata_attributes pour savoir à quelle colonne/table se tourner.

Approches Possibles

voici deux approches possibles auxquelles je peux penser.

approche 1: table EAV unique avec plusieurs colonnes

créer trois colonnes différentes à la table patient_metadata - une pour chaque valeur type.

CREATE TABLE patient_metadata (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_varchar_value VARCHAR(50),
  attribute_number_value NUMBER,
  attribute_id_value CONSTRAINT fk_pm_values REFERENCES some_table_of_values(value_id)
);

Approche 2: Plusieurs EAV tables

créer trois tables patient_metadata différentes - une pour chaque valeur type.

CREATE TABLE patient_metadata_varchar (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value VARCHAR(50) NOT NULL
);

CREATE TABLE patient_metadata_number (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value NUMBER NOT NULL
);

CREATE TABLE patient_metadata_id (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value CONSTRAINT fk_pm_values REFERENCES some_table_of_values(value_id) NOT NULL
);

Autres Approches?

existe-il d'autres approches?

en bref, je veux respecter l'intégrité relationnelle autant que possible et permettre à la base de données de connaître le valeur tapez pour qu'il puisse effectuer la validation de base. Cependant, je crois que les deux de les approches ci-dessus nécessiteront un certain type de vérification manuelle de l'intégrité (l'approche 1 exige une vérification qu'une seule colonne attribute_value soit remplie, etc.).).

les types de requêtes que je vais effectuer seront typiques (par exemple, extraire une liste de valeurs pour un métadonnées l'attribut, récupérez une liste de valeurs pour un patient donné ( entity) et des métadonnées l'attribut, etc.). Je crois que je vais avoir besoin d' la requête pour l' valeur tapez dans la plupart des cas afin de savoir quelle colonne ou table à interroger. Tout autre moyen de contourner cela?

Quels sont les avantages et les inconvénients de toutes les approches (performance, structure des requêtes, etc.)?

première affiche, alors merci à l'avance et n'hésitez pas à nous faire part de vos commentaires sur le formatage ou d'autres précisions!

25
demandé sur Community 2013-08-07 18:07:55
la source

2 ответов

C'est un problème connu. Le problème avec l'approche que vous avez mentionné est que vous avez besoin de connaître le type de l'attribut avant de requête. ce n'est pas la fin du monde parce que vous gérer les métadonnées, mais encore...

deux solutions possibles pourraient être

  1. à l'aide d'un varchar2 type de données pour représenter tous les types de données dans un format. Les nombres et les caractères ne sont pas un problème, les valeurs de date peuvent être écrites d'une manière prédéfinie (c'est comme implémenter to_String() en tout OO conception.)
  2. utiliser le ANYDATA type de données. j'ai personnellement joué avec elle mais a décidé de ne pas utiliser il.
4
répondu haki 2013-08-24 11:07:37
la source

le plus simple, le plus performant, etc est de convertir toutes les valeurs de la base de données en chaînes. Les problèmes tels que ceux qui sont indiqués sont généralement évidents, et même les colonnes bien dactylographiées souffrent exactement du même genre de problèmes, qui s'expriment habituellement sous forme de problèmes de performance.

avec un peu de soin, vous pouvez maintenir l'ordre de collation, si cela importe (par exemple en formatant les dates comme Année/Mois/Jour), et la validation des types ne devrait pas être faite par la base de données de toute façon car il est trop tard. Négatif les nombres sont une douleur, comme les flotteurs, mais il est très rare d'indexer par un nombre qui peut être négatif ou un flotteur, et dans la mémoire tries sont généralement rapides.

lorsque le type de données n'est pas évident, ou doit être connu par un processeur en aval, ajouter une colonne type.

généralement, toutes les contraintes d'intégrité par rapport aux valeurs de la colonne peuvent être vérifiées avant que l'enregistrement ne soit écrit, soit en code (Bon), soit en déclencheurs (pas si bon). Essayez d'utiliser les fonctionnalités natives avec les différents types ne vous mèneront que jusqu'à présent, et n'est probablement pas si utile de toute façon que les valeurs ont souvent de nombreuses contraintes spécifiques à l'entreprise de toute façon par exemple Date de naissance doit être non-null et après 1900.

pour la performance, utilisez des index composés incluant l'entité et l'attribut comme préfixes. Les index peuvent être partitionnés par le préfixe entity-attribut, réduisant l'impact de la profondeur supplémentaire de l'index, et ils se compriment vraiment bien( le préfixe se compressera à un ou deux octets), de sorte que la taille la différence est minime.

interroger à partir des tables EAV est souvent mieux fait dans des vues qui déballeront les entités pour vous de sorte que la structure peut être retournée à quelque chose comme vous vous y attendriez, bien que cela puisse être hors de propos si vous avez affaire à des colonnes variables, par exemple dans des formes de patients qui sont caractérisées par un grand nombre d'éléments variables selon l'histoire. Alors il est probablement plus facile à traiter dans votre logique d'affaires.

Enfin, de nos jours, ce genre de les données ne sont tout simplement pas stockées dans le style de la base de données relationnelle axée sur la colonne. Il est habituellement stocké sous forme de document XML (ou JSON) (types XML dans Oracle), et la plupart des bases de données offrent une certaine capacité de traitement XML natif afin de rechercher et de manipuler de telles données. Cela est acceptable pour le stockage et la récupération de formulaires normaux, mais tend à faire des requêtes arbitraires telles que "donnez-moi tous les patients de plus de 60 qui ont eu une pneumonie au cours de la dernière année" plutôt lent, ou un peu plus impliqué que l'indexation inversée étiquetée est nécessaire. Néanmoins, il est intéressant de voir si une approche axée sur le document/le texte est une meilleure solution.

Bonne chance!

2
répondu emperorz 2013-09-04 19:54:41
la source