relationship between tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

relationship between tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-15 : 09:19:21

deepak writes "HI guys,
THis is regarding Relationship b/w 4 tables.
There are four tables which are not related to eachother,no relationship exists between this table as of now.

Hierarchy of table postions is same below :
1) JDSector
2) JDDept
3) JDSection
4) JDUnit

JDDept:
DeptID int NOT NULL ,
Department nvarchar (255)

JDSection :
SectionID int NOT NULL ,
SectionName nvarchar (255)

JDSector :
SectorID int NOT NULL ,
SectorName nvarchar (255)

JDUnit :
UnitID int NOT NULL ,
UnitName nvarchar (255)

USer will add values to table and also can delete/update values.
THere should be flexiblity for this.

for example:
1)user can add new unit under sector directly OR can add new unit directly or add new unit under department directly or can add new unit under section directly .
2)user can add new section under sector directly OR can add new section under department.
3)user can add new department only under sector.so i explained hierarchy of table as shown in above.
4)user can add new sector only in sector table not in any where.
5)user can add new unit under section and in turn same sector can under department and in turn same department can add under sector.IT can go in this hierachy also and same thing applieswhil adding new section.
Same way it should update properly in all tables when user delete/update values

The above criteria are just scenario which i need to design tables and to have relationship.ABove scenario can grow ,i just given sample scenarios.
So my question is
1) is i really wann have realtionship b/w all 4 tables to achieve my full my requirement
2)if really wann have relationship ,can anybody help me out to design tables and to have relationship for same.

Thank u
Deepak"

Andraax
Aged Yak Warrior

790 Posts

Posted - 2007-02-16 : 03:48:20

Hey.

I guess I would skip the four tables and just go with two, something like this:

JDHierarchy:
ID int not null,
Name nvarchar(255),
TypeID int not null,
parentID int null

JDType
TypeID int not null,
TypeName varchar(50)

All the departments, units, sectors and sections would reside in the same table, the Type table specifiying what kind it is. The parentID links upwards to the parent. If there is no parent it would be set to null.

/Andraax

PS. You'd probably have to use some kind of triggers to enforce your rules about this hierarchy.

deepak_Dotnet
Starting Member

32 Posts

Posted - 2007-02-18 : 02:04:43

HI andraax,
Thanks for the reply.
So is there any relationship needed b/w JDHierarchy and JDType Tables..?

AT last u have told me that "You'd probably have to use some kind of triggers to enforce your rules about this hierarchy."
SO please can u explain what kind of trigger is required and why it is required..?

THank u
Deepak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources