database design - Junction Table & Normalization Question -


i having hard time trying figure out if following design pattern acceptable. have following requirements (and other more) relational model:

1) must able represent applications (such appa, appb, appc), each 1 it's own set of attributes.

2) every applications can communicate through different channels internet (e-mail, twitter, facebook), phone (sms, mms, etc.) there's many-to-many relationship between programs , channels.

3) there set of pre-defined identifiers (addresses, phone-numbers, login accounts) can shared many programs, that, again, there's many-to-many relationship between programs , identifiers.

4) same identifier can send several types of messages, , can programs (again, many-to-many), need able restrict usage of identifiers communications type on per-application basis.

basically, did create 4 tables, program, channel, ident , communicationtype store information each of these and, instead of creating junction tables (program, channel), (program, identifier), , on complicate design, created single table consisting of primary keys of these 4 tables unique constraint on (program, channel, ident, communicationtype). now, each record of table linked given communication.

of course, solves problem in pretty easy way, questioning myself whether acceptable @ of if defeats principles of normalization. can please give me opinion?

basically, did create 4 tables, program, channel, ident , communicationtype store information each of these and,

that's fine idea.

instead of creating junction tables (program, channel), (program, identifier), , on complicate design, created single table consisting of primary keys of these 4 tables unique constraint on (program, channel, ident, communicationtype).

you need careful of 1 thing when design tables this. structure, has key {program, channel, ident, communicationtype}, allows every possible combination of program , channel, of channel , ident, of program , communicationtype, , on. that's bad idea.

the same identifier can send several types of messages, , can programs (again, many-to-many), need able restrict usage of identifiers communications type on per-application basis.

and that's makes bad idea. seem saying not every combination of ident, program, , communicationstype valid.

store valid combinations in own tables. use foreign key references maintain data integrity.

build table has key {program, ident, communicationstype}. table has key {program, channel, ident, communicationtype} can set foreign key reference it.

build many tables takes implement constraints know of. more tables means data integrity checks simpler. (you might need more tables ones mentioned. don't assume need have 2 columns; might need more.)

it's not @ clear need table keyed {program, channel}. if do, need build tables along these lines. (air code.)

create table pc (     program_name varchar(10) not null references programs (program_name),     channel_name varchar(10) not null references channels (channel_name),     primary key (program_name, channel_name) );  create table pict (     program_name varchar(10) not null,     channel_name varchar(10) not null,     comm_type varchar(10) not null references communication_type (comm_type),     primary key (program_name, channel_name, comm_type),     foreign key (program_name, channel_name)          references pc (program_name, channel_name)  );  create table your-table-name (     program_name varchar(10) not null,     channel_name varchar(10) not null,     comm_type varchar(10) not null,     ident varchar(10) not null,     primary key (program_name, channel_name, comm_type, ident),     foreign key (program_name, channel_name, comm_type)          references pict (program_name, channel_name, comm_type),     foreign key (ident) references ident (ident) );

add other columns needed. in cases, you'll find need overlapping foreign keys. don't think need them here, wrong.

i'm not sure mean "if defeats principles of normalization". table has four-column primary key doesn't violate of normal forms for reason alone, although might other reasons. failing implement known constraints generally, um, sub-optimal design, not because violates of normal forms.


Comments

Post a Comment

Popular posts from this blog

c++ - Is it possible to compile a VST on linux? -

for class project i'm attempting write vst plugin backed cuda. current cuda workflow on linux box, i'd prefer compile , link there. according wikipedia , should possible (i couldn't find steinberg documentation relevant linux) can't find makefile or instructions on how build if aren't using xcode or visual studio. i'm vst 3 sdk doesn't support linux. when try compile plugin under linux, error: ./base/source/fatomic.cpp:39:30: fatal error: libkern/osatomic.h: no such file or directory this issue caused following code in "vst3 sdk/base/source/fatomic.cpp" #if mac #include <libkern/osatomic.h> #if mac_os_x_version_min_required > mac_os_x_version_10_4 #define native_atomic_type (volatile int32_t*) #else #define native_atomic_type (int32_t*) #endif #elif windows #include <windows.h> #endif but hope compiling under linux work vst sdk 2.4. reading. jvstwrapper seems run on lin...
Read more

java - Output of Eclipse is rubbish -

i want write program in eclipse language different english. when display output using system.out program, displays output correctly in console editor. when accept input console , output it, output rubbish. how can solve it. many thnaks i'm not sure if problem, have character encoding problem. make sure you're using unicode, utf-8. you can read more @ official docs converting non-unicode text .
Read more

jquery - Confused with JSON data and normal data in Django ajax request -

i read json internet still have not got grasp of it. reading article http://webcloud.se/log/ajax-in-django-with-jquery/ i not understood first part function using json def xhr_test(request, format): if request.is_ajax(): if format == 'xml': mimetype = 'application/xml' if format == 'json': mimetype = 'application/javascript' data = serializers.serialize(format, examplemodel.objects.all()) return httpresponse(data,mimetype) # if want prevent non xhr calls else: return httpresponse(status=400) my main problems are from function getting format variable does format json mean data given function json or data recived json can give me simple example ouput of function data = serializers.serialize(format, examplemodel.objects.all()) how use data when response in jquery function if don't use json in above function how input , response chnage thanks ...
Read more