0

I've been stuck on a solution for the problem I am about to describe below. If there is something that needs clarification please let me know so that I can update my post. First of all, let me explain the database structure. I have the following tables (and more, but they are not part of this topic):

1) Divisions 2) Companies 3) Sectors 4) Products

All the above tables are related.

1) Divisions has many Companies, but Companies can only belong to a single Division. 1-* 2) Each Company can have one or more Sectors, but a Sector can belong to a single Company. 1-* 3) Each Sector can have one or more Products and each Product can belong to one or more Sector. -

My problem(s) are below: Given the above, regardless of Companies, I need to generate or retrieve all Sectors under a given Division. But the trick is they need to be merged based on their names not ids, so suppose you have Division 1 and under it Company A and under that Sector A. Of course Sector A could exist again as another entity in another Company under the same name, but different ID (other than Company A) that also falls under Division 1. So in that new "merger" Sector A should be distinct meaning there should be a single entity for it, not more. Let us call the table that stores these mergers table X. Now table X (which contains unique Sectors under a given Division regardless or their respective Companies) should be mapped to another table (Table Y) that will contain documents and other files. Now the second issue is that Table Y's documents need to be related to Products that are mapped to Sectors listed in table X. Of course if Product 1 is under Sector A and Sector B its documents might be different, just because it belongs to two different Sectors.

How could the above be translated into SQL Tables / Views or whatever is needed? And if that's not possible, what is the right approach to solving the issues? Also, what happens if a Sector is deleted? or its name was updated? Or a product update/delete? What is the best thing to do in this particular case.

Note: the tables 1, 2, 3, 4 listed above are already created and functional. But I am required to add this extra functionality described as the problem above. I cannot do anything here to change the logic and requirements.

Using MS SQL Server 2008 R2.

Many thanks in advance. Please let me know if anything needs clarification.

EDIT : How the structure currently is vs. How it should become generated when adding documents.

enter image description here

Expected output:

enter image description here

The merger here is based solely on Product names. But now, what happens if Product A has been Updated? Deleted? How does that reflect on the output and mapped Documents?

Thanks again.

(BTW: I can generate that output it's no issue given the first data Model (First image) But I need that placed somehow in a table which proper relationships so that I can map Documents to Products based on all the above.)

user1027620
  • 2,745
  • 5
  • 37
  • 65
  • 1
    You said `a Sector can belong to a single Company` and then you said `Of course Sector A could exist again as another entity`. This sounds like a contradiction to me. – Ilya Kogan Nov 24 '12 at 02:18
  • Yes I'm sorry about that. Sector A could belong to Company 1 and Company 2 (as a different entity but having the same name). Need to merge those based on their names even if Sector A in company 1 has an id of 1 and Sector A in company 2 has an id 2. But because both fall under the same division they need to be merged into 1 entity. – user1027620 Nov 24 '12 at 02:21
  • Please include table structure, sample data, and expected output. That'd be much easier to look at. – MikeSmithDev Nov 24 '12 at 02:37
  • You can easily get the unique sectors under a division (with e.g. divisionid = 1) by SELECT DISTINCT d.dname, s.sname FROM division d JOIN company c ON c.divisionid = d.divisionid JOIN sector s ON s.companyid = c.companyid WHERE d.divisionid = 1 I really do not understand what do you require next. – Rachcha Nov 24 '12 at 07:10
  • You probably want to start somewhere like: http://stackoverflow.com/questions/377375/a-beginners-guide-to-sql-database-design or another introductory book/site for database design basics. – MikeSmithDev Nov 25 '12 at 21:45

0 Answers0