mapping of areas with multiple users

Question

I have areas like sector 1, sector 1 a, sector 1 b, sector 1 c and multiple cable operators who are working in either full sector(i.e sector 1) or any of the sub sectors. I have created table of cable operators and want to map them with areas. If I set up area table like sector 1, sector 1 a, sector 1 b, sector 1 c each with their own Primary Key then how can I reference these sectors in single row of cable operators provided that we have to get the cable operators working in that particular sector.

My table structures are as follows:

Operators

| id | name
| 1  | 'abc'
| 2  | 'def'
| 3  | 'ghi'

areas

| id | name
| 1  | 'sector 1'
| 2  | 'sector 1a'
| 3  | 'sector 1b'
| 4  | 'sector 1c'
| 5  | 'sector 1d'
| 6  | 'sector 2'
| 7  | 'sector 2a'
| 8  | 'sector 2b'
| 9  | 'sector 2c'
| 10  | 'sector 2d' 

I have operatorsareas table where I have map operators with areas as follows: operatorsareas

| op_id | area_id
| 1     | 1
| 2     | 1
| 3     | 1
| 1     | 7
| 2     | 8
| 3     | 7

Now I have used this query which gives me no result:

select o.id, o.name from operator as o
where not exists(select * from areas a where id in (1,7,8) and not exists(select * from operatorareas as oa where oa.operatorid=o.id 
and oa.areaid = a.id))

I have taken the reference of following link:

SQL query through an intermediate table

I need a guidance regarding structuring of the tables.

Answer 1

Initial Problem

Your Sector/Subsector designation breaks 1NF:

• Each domain [column] must be Atomic wrt to the [datatypes available in the] platform.

That is a gross Normalisation error, which will have horrendous consequences downstream. The correction is:

• Sector is one datum, one column, eg. Sector 1
• SubSector is a separate datum, a separate column, eg. a, b, c

The Data • What is it ?

I need a guidance regarding structuring of the tables.

Ok. But what the data actually means, is not at all clear.

From the little info you have given, the following Predicates can be derived:

• Each Operator is assigned to 0-or-1 Area
  ^{Assumption: an Operator cannot be in more than one place at a time
  Assumption: an Operator may not be assigned}

• Each [assigned] Area is one of { Sector | SubSector | Unassigned }
  AreaType is the Discriminator

• Each Sector comprises 0-to-n SubSectors
  Each Sector is occupied by 0-to-n Operators

• Each SubSector is occupied by 0-to-n Operators

Please check and ensure that each is true (otherwise the data model is garbage).

Relational Data Model

Assuming those Predicates are correct, the Normalised Relational data model is:

Subtype • Exclusive

operators are working in either full sector or any of the sub sectors

• What you are seeking in Logic terms is an OR Gate, in Relational terms, it is an Exclusive Subtype
  • Refer to Subtype for full details on Subtype implementation.

Note • Notation

• All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993

• My IDEF1X Introduction is essential reading for beginners or novices, wrt a Relational database.

The Query • What is it ?

Now I have used this query which gives me no result

We do not know what result set you are attempting to obtain.

• At this point, it does not appear to be related to the linked Question & Answer.

• Please explain what result set you would like to obtain, in English. Hopefully observing the given data model.

• Supplying the required SQL would then a simple matter.

Enjoy. Please feel free to ask specific questions.