sql - Dual use of composite foreign keys -


i'm modelling hierarchy of 3 entities therapy planning software. therapy understood number of medications given patient on number of days. want able cancel therapy on given day, in write-only-once fashion (for quality control certification purposes).

here's concrete question: ok reuse part of composite foreign key foreign key? in case composite key points medication table day table, points therapy. therapy id included in composite foreign key in medication table, use foreign key, making querying more easy?

the table definitions should (modulo hickups in raw sql skills, employ kind of orm):

create table therapy (     "id" integer not null,     "start" date not null,     primary key (id) );  create table day (     "therapy_id" integer not null,     "day" integer not null,     "revision" integer not null,     "comment" text;     "cancelled" boolean not null;     primary key (therapy_id, day, revision), foreign key(therapy_id) references therapy (id) );  create table medication (     "id" integer not null,     "therapy_id" integer not null,     "day" integer not null,     "revision" integer not null,     "substance" varchar(100) not null,     "dosage" varchar(50) not null,     primary key (id), foreign key(therapy_id, day, revision) references day (therapy_id, day, revision), -- wondering if ok     foreign key(therapy_id) references therapy (id) );

please note relation day medication one-to-many, e.g. on day 3 want plaster patient deadly mix of ibuprofen, paracetamol, aspirin, ground coffee beans , shot of jaegermeister.

here's started with.

create table medication (     "id" integer not null,               -- don't need this.     "therapy_id" integer not null,     "day" integer not null,     "revision" integer not null,     "substance" varchar(100),     "dosage" varchar(50),     primary key (id),                    -- drop this.     foreign key(therapy_id, day, revision)          references day (therapy_id, day, revision),  -- ok, should pk.     foreign key(therapy_id) references therapy (id) -- don't need this. );

the column "id" serves no useful purpose, , has instead convinced you don't need unique constraint on real key: {therapy_id, day, revision}. replace useless primary key constraint on "id" primary key constraint on {therapy_id, day, revision}. drop last foreign key constraint. know "therapy_id" reference existing row, because foreign key constraint in table "day" requires it.

that leaves this.

create table medication (     "therapy_id" integer not null,     "day" integer not null,     "revision" integer not null,     "substance" varchar(100),     "dosage" varchar(50),     primary key (therapy_id, day, revision),     foreign key(therapy_id, day, revision)          references day (therapy_id, day, revision) );

it troubles me "substance" , "dosage" nullable. think little.


Comments

Post a Comment

Popular posts from this blog

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

c# - Resource not found error -

i have problem accessing method on homecontroller. show code of method : [httpget] public actionresult decriptidentifiantetredirige(string login_crypter, string mdp_crypter) { string loginacrypter = _globalmanager.protegemotdepasse(login_crypter); string mdpacrypter = _globalmanager.protegemotdepasse(mdp_crypter); user userapp = new models.user(login_crypter, mdp_crypter); if (userapp.authentificationvalidee(userapp.userlogin, userapp.password)) { session["name"] = userapp.userlogin; return redirecttoaction("accueil", "home"); } else { return redirecttoaction("validerauthentification", "home"); } } then in routeconfig.cs wrote route : routes.maproute( name: "authentificationapresdecryptage", url: "{controller}/{action}/{login_crypter}/{mdp_crypter}", defaults: new...
Read more