sql server - Database/Application Design for Group-able Entity -

just want ask input what's best database/application design following scenario

1. i have table called computers
2. each products can grouped following:
   • vendor (e.g. 'sony', 'apple','hp'),
   • type (e.g. 'laptop','personal desktop','server')
   • size (e.g. 'small','medium','large')`
   • more things come business requirement changes (maybe touchscreen in future)
3. these computers can grouped repairers when
   • 'pc repairers' can fix small personal desktops , small laptops
   • 'better computers' can fix all sony computers
   • 'repair less' can fix all hp servers not large
4. as can see there may chances things can overlapped
   • small sony laptops can fixed both 'pc repairers' , 'better computers'

what term kind of database design? not know how search online

would love hear answers !

thanks

i don't know of name kind of design you're describing. might call feature-based association.

if rules require equality of subset of features, use following schema implement it:

computers (id pk, vendor, type, size) repairers (id pk, name) can_repair (id pk, repairer_id fk, vendor null, type null, size null) 

the can_repair table used associate repairers classes of computers can repair. note doesn't handle exclusions (e.g. "all hp servers not large"), inclusions (e.g. "small hp servers" , "medium hp servers").

an example of how query repairers given computer:

select distinctrow r.* computers c inner join can_repair cr     on coalesce(cr.vendor, c.vendor) = c.vendor     , coalesce(cr.type, c.type) = c.type     , coalesce(cr.size, c.size) = c.size inner join repairers r on cr.repairer_id = r.id c.id = 123 

here's sql fiddle demonstrate it.

note proof of concept. in real world record vendor keys instead of names , represent vendors , repairers subtypes of organizations. use enums or lookup tables each feature rather varchars.

edit: can add exclusions model easily. add table:

cant_repair (id pk, repairer_id fk, vendor null, type null, size null) 

then modify query so:

select distinctrow r.* computers c inner join can_repair cr     on coalesce(cr.vendor, c.vendor) = c.vendor     , coalesce(cr.type, c.type) = c.type     , coalesce(cr.size, c.size) = c.size inner join repairers r on cr.repairer_id = r.id left join cant_repair xr     on xr.repairer_id = r.id     , coalesce(xr.vendor, c.vendor) = c.vendor     , coalesce(xr.type, c.type) = c.type     , coalesce(xr.size, c.size) = c.size c.id = 123 , xr.id null