sql - foreign key database help needed -

i'm not sure how title question, here situation.

i have 3 sql tables follows:

one lookup table lists names of adjectives along number (primary key value) associated each adjective.

one object table connects other tables outside situation need with.

and 1 table connects object table adjectives lookup table. connecting table has 3 foreign keys. 1 connects primary key of lookup table, , 1 connects reference key in object table.

if wanted waste disk space redundant database entries, wouldn't here asking help, because example:

in lookup table, have following rows of values:

 1,"sweet" 2,"bitter" 3,"sour" 

in object table, have primary key representing object, field object name, , reference key represent adjectives object. now, assume has 2 row these values: row 1: (primary key 1,"raspberry",reference key value 1), row 2: (primary key 2,"strawberry",reference key value 4).

let's first step make raspberry "bitter sweet". ![enter image description here][1] in connecting table (that connects object adjectives), have 1 field constant value (for object) , other field individual values, put in (1,1) , (1,2) apply both "bitter" , "sweet" raspberry.

let's want make strawberry "bitter sweet" well.

then in connecting table, put in (4,1) , (4,2) apply both "bitter" , "sweet" strawberry.

the problem here first field value in connecting table because values 1 , 4 mean "bitter sweet". need condensed can use either 1 or 4.

i'm trying figure out easy way automate whole process. also, want able prioritize each word. example, want decide order adjectives used.

so far example, have come untested idea:

select keytoobject  connectingtable  keytoobject  in (select keytoobject                         connectingtable                         keytoadjective=1)    , keytoobject in (select keytoobject                        connectingtable                        keytoadjective=2)  group keytoobject  having count(*) = 2 

basically, i'm trying search connecting table see if exact same adjective set exists , if does, take key , reuse instead of make new key same adjectives.

also, part stumps me when take adjectives, want retain priority well.

does have better idea on how can tackle problem?

update: added tables show going on. since have less 10 reputation, had post links images. hope posted them right.

database need with erd same database

the word realized looking normalization. want normalize connection table.

in example (based on images), want these words stored in database: bitter sweet raspberry bitter sweet strawberry super crazy sour candy crazy sweet sugar (sorry, meant make bottom left aid value 4 instead of 2) sweet tooth.

for bitter sweet raspberry , bitter sweet strawberry, can change sweet bitter raspberry , sweet bitter strawberry swapping priority values when cid 33.

what want software this:

first asks me enter names of objects. next stores object name , creates random id object in object table

next asks me select adjectives apply object. choose 3 of them. give each adjective priority number computer knows order come in later use. (lets call object "crazy bitter sweet strawberry")

once done, software create 3 rows of values in connecting table apply set of adjectives object. (1 crazy, 1 bitter, 1 sweet)

say want add new object. stored in objects table along new id. time want use 2 adjectives used before want avoid having create rows of data if possible. (lets call "bitter sweet raspberry")

then third object, add 4 adjectives, 1 matches adjective in object. (lets call "ridiculously super crazy awesome girl")

then want add 50 more objects few adjectives 1 "crazy", , 50 few adjectives 1 adjective "sweet"

the problem here computer generate 50 rows word "crazy" , 50 rows word "sweet". somehow want system reuse existing adjectives if match up.

basically system word building application people can add , remove adjectives words.

i want avoid ridiculously large database if possible. way feel large database makes sense if every adjective used unique word in cases.

something tells me need table dont know if i'm right.

oh, , reference, aid stands adjective id number, , cid stands connecting table id. table, 1 object can have many adjectives like, , 1 group of adjectives can apply many objects like.

rather changing objects table, better off adding 2 more tables maintain adjective "groups".

but assumes same groups of adjectives (and priority) reused regularly. may find original database structure more suited otherwise, joins decrease performance.

i've written in tsql sql server haven't tagged database using.

create table [objects] (     id int identity(1,1) primary key,     name varchar(255) not null unique );  create table [adjectives] (     id int identity(1,1) primary key,     name varchar(255) not null unique );  create table [adjectivegroup] (     groupid int identity(1,1) primary key );  create table [adjectivegroupdetail] (     groupid int not null references [adjectivegroup] (groupid),     aid int not null references [adjectives] ([id]),     [priority] int not null,     unique (groupid, aid),     unique (groupid, [priority]) );  create table [connectingtable] (     cid int not null references [objects] ([id]),     groupid int not null references [adjectivegroup] (groupid),     unique (groupid, cid) );  set identity_insert [objects] on; insert [objects] ([id], [name]) values (1, 'raspberry'), (2, 'strawberry'); set identity_insert [objects] off; set identity_insert [adjectives] on; insert [adjectives] ([id], [name]) values (1, 'sweet'), (2, 'bitter'); set identity_insert [adjectives] off; set identity_insert [adjectivegroup] on; insert [adjectivegroup] (groupid) values (1); set identity_insert [adjectivegroup] off; insert [adjectivegroupdetail] (groupid, aid, [priority]) values (1, 1, 2), (1,2,1); insert [connectingtable] (cid, groupid) values (1, 1), (2, 1);