schema - Database design of a record keeping system -


i in process of designing database of record keeping system. system has 4 types of record / forms.

here design.

first, 4 forms has common input fields such name, age , address. separate common input fields in table , add foreign key.

for example:

common_fields  id  name  age  address  form_fk_id (foreign key form)  form_type_number(this define kind of form user used)  form1  id  birthdate  occupation  etc..  form 2  id  mother_name  father_name  etc..  form 3  id  school_address  school_name  etc..

basically, that.

form_fk_id - foreign key connect common input fields table , form table

form_type_number - useful knowing form retrieve.

i unsure of design. dont know if best way design in.

you don't want fk reference in common table. common table have key , type indicator detail tables reference their fks.

common_fields  id  form_type (defines kind of form) must contain 1, 2 or 3  name  age  address  constraint uq_common_fields_id_type unique( id, form_type )

the unique constraint makes fk reference (id, form_type) possible.

form1  id  form_fk_id (with id, fk common) must contain 1  birthdate  occupation  etc..  form2  id  form_fk_id (with id, fk common) must contain 2  mother_name  father_name  etc..  form3  id  form_fk_id (with id, fk common) must contain 3  school_address  school_name  etc..

assume form_type fields small integer , constrained in common table values (1, 2, 3). in form1 table, field constrained value 1, in form2 2 , in form3 3.

when new form created, common data inserted common_fields table, generating unique value id. form_type field contains value 1, 2 or 3 depending on type of form.

say value 1, matching record can inserted form1 table.

an entry same id value cannot written either of other form tables. value of form_fk_id of 1 not allowed each table has check constraint making sure field must contain proper value. value other 1 not find matching set of values in common table.

this makes form defined particular form type in common table can have associated entry in correct form table.

when user wants see details of form 123, locate 123 in common table , join details correct table according form type.

actually, have 3 views, each view showing complete details of each form. triggers on views allow app code manipulate each type of form if form data in 1 complete row instead of broken across 2 tables. hides structure app , makes sure structure correctly maintained.


Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more