sql server - SQL Table Integrity -

-

i'm using sql server 2014 i'm looking design solution.

i have 3 tables need connect ensure integrity. i'm removed tables , columns sake of clarity.

enter image description here

group parent of type.

group: widgets types: blue widgets, red widgets

i want store data @ group/category level, instance:

group: widgets categories: widgets men, widgets women

categories should contrained @ type level

type: blue widgets category: widgets men  type: red widgets category: widgets women

therefore i'm trying ensure data enter typecat valid in category , type's group exist in groupcat.

i'm sure there kind of pattern this, non db guy, i'm not sure how work out.

at risk of spending far time on question, i'm going try show how difficult untangle. use thicket of abstract nouns -- type, category, group -- in varying combinations. change nomenclature between illustrated tables , question text. don't state keys are. make declarations, , expect reader make right associations , inferences.

group parent of type.

so groupid identifies grouptype; grouptype has typeid. anyway, that's how i'd interpret "parent".

categories should contrained @ type level

i wasn't able make sense of given next statement:

ensure data enter typecat valid in category , type's group exist in groupcat

not sure "that" category is, rule seems 

typecat.typeid exists in grouptype.typeid grouptype.groupid exists in groupcat.groupid

but said

i want store data @ group/category level

which seem imply new data coming left, were, , new rows in groupcat must meet constraints:

groupcat.groupid exists in grouptype.groupid -- opposite above groupcat.catid   exists in typecat.catid {groupcat.groupid,typecat.typeid} exists in grouptype

those rules can expressed in sql, i'm not sure should. , i'm not sure they're intend. i'm throwing darts and, if not blindfolded, let's lighting leaves desired.

in general, statement a must have b in sql means there table b representing domain of values of type, , foreign key on column in values must come domain. logic, there should tables groups, categories, , types in model. pattern of last rule above -- 2 tables must joined determine if meet third domain -- occur, rarely. when crops up, it's cause things on , ensure model reflect reality.


Comments

Post a Comment

Popular posts from this blog

javascript - Laravel datatable invalid JSON response -

-
everything work fine on code below fetch user logs: js: <script> $(document).ready(function () { var table =$('#systemlogs').datatable({ responsive: true, processing: true, serverside: true, "language": { "url": "/datatables/media/plug-in/persian.json" }, ajax: '{!! url('/admin/systemlogs/data/systemlogsdatatable') !!}', columns: [ { data: 'name', name: 'name' }, { data: 'message_text', name: 'message_text' }, { data: 'remote_addr', name: 'remote_addr' }, { data: 'log_created_at', name: 'management_logs.created_at' }, { data: 'log_updated_at', name: 'management_logs.updated_at' }, ] }); }); </script> routes: route::get('/systemlogs/data/systemlogsdatatable','systemlogscontrolle...
Read more

java - Exception in thread "main" org.springframework.context.ApplicationContextException: Unable to start embedded container; -

-
i getting error when run spring application: exception in thread "main" org.springframework.context.applicationcontextexception: unable start embedded container; nested exception org.springframework.context.applicationcontextexception: unable start embeddedwebapplicationcontext due missing embeddedservletcontainerfactory bean. @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.onrefresh(embeddedwebapplicationcontext.java:124) @ org.springframework.context.support.abstractapplicationcontext.refresh(abstractapplicationcontext.java:476) @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.refresh(embeddedwebapplicationcontext.java:109) @ org.springframework.boot.springapplication.refresh(springapplication.java:691) @ org.springframework.boot.springapplication.run(springapplication.java:320) @ org.springframework.boot.springapplication.run(springapplication.java:952) @ org.springframework.boot.springa...
Read more

sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -

-
msg 245, level 16, state 1, line 1 conversion failed when converting varchar value '8:45 am' data type int. script declare @specimenid bigint = 0, @script varchar(max); declare @starttime int = (select convert(int,substring(start_time,0,charindex(':', start_time)) - 12) appt appt._id=112601) --select @starttime set @script = 'select distinct ptlast_name + '', '' + pt.first_name patient_name, dbo.formatdate(pt.dob,''mm/dd/yyyy'') dob, (convert(varchar, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end)) + ''/'' + convert(varchar, (datediff(m, dateadd(yy, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end), d...
Read more