sql - Using a regex as a DB field -

i have been trying quite on top , opinion it.

i'm using postgres db store product information, family, using 4 digit code. let instance, product can have 1000, 1001 , 1002 while product b has 1003 , 1004.

i want retrieve family code.

i 2 tables, 1 family name, key , other info, 1 codes , link familly requested. then, request :

select familyname  family, familycode  family.pk = familycode.fk_family , familycode = '1000'  

would trick i'm quite greedy, wondered if wasn't possible use regex , single table primary key, label , regex give code.

following previous example, have :

'pk' 'producta' '100[0-2]{1}'  'pk' 'productb' '100[3-4]{1}' 

but then, can work , give me "producta" single request entering '1000' code?

thanks attention , answers

best regards

edit :

i'm adding exemple of workflow have reference table family, containing id, label , code.

'pk' 'producta' 'code1'  'pk' 'productb' 'code2' 

depending of family, soft act in consequence. if have product a, retriev in reference table operation linked product

let following stap

select operation table_operation productref = 'producta'

to operation, need familly. familly, have code.

as there lot of differents codes single family, thought using regex in table. that's all

i dont know why want complicate much.

in single table can have 2 fields

 select family, familycode  table  family = 100 , familycode in (0,1,2) 

you same 1 field , regexp, have carefull because postgres can use simple indexes left-anchored expressions value '00%' (important big tables).

also problem design if later want add producta inventory stuck rigid model. why first solution 2 separated table better.