Database Design

Hi all,
I am designing a database as a practice exercise, but have come across a problem.

Here is my design:


Note: The ... in the tables are just placements symbolising that there are multiple irrelevant (to this thread) fields.

The problem is with the field highlighted in italics whilst the primary keys are in bold. I need to somehow retrieve that ID, but I can't create a many-to-many relationship.

I have tried to think of a solution to the problem, but don't know how to solve it

Could anyone help?

can you be more specific on what you want?

eventually give an example of what data you have and what the result you want.

Well, the Branch table is going to hold the different branches where the banks are located.
The Employee table holds the employees which work within those banks at the different branches.
I added in the Manager table to create 2, one-to-many relationships.
Along with these tables there will also be customer details, but that is not important for the problem at hand.

So, each row in the Employee table holds an employee, and each employee has a manager. I need to somehow get that manager's EmployeeID and detail it in the row. However, I do not know how to achieve this because the data I need is from the same table.

Some example data for each table:
Branch:
BranchID City Postcode Manager
'001' 'London' 'W1 2ED' 'Fujin'

Manager:
ManagerID EmployeeID
'A00' '00A'

Employee:
EmployeeID Name Steet Manager Branch
'00A' 'Fujin' '123 Street' 'Null' '001'
'00B' 'John' '124 Street' 'A00' '001'

Simplify your design.

Eliminate the un-needed manager table; all manager's are already employees....

Let the ManagerID field in the employee table contain NULL for those that are managers, and an EmployeeID of the manager for those that are managed.

Then do "self-join":

What you have responded is exactly what I thought of doing and in fact how I started off, but would it be possible to do it any other way using only fields and tables?

Lot's of things are possible.

But why over-complicate things?

What do you stand to gain from doing so?

By giving a manager an arbitary ID - now you can no longer reference him as an employee without one extra layer of indirection.

THere is no absolute "right" or "wrong": there are only your design goals and a best-fit implementation.

You could treat managers and employees as separate entities, and model that relationship by way of two tables: employee, manager.

But what do you gain from doing so? I now see that you likely followed this approach to it's conclusion, which is why you are mapping a manager ID to an employeeID. Consider using an attribute (field) in your employee table instead (IsManager or similar)

With a self-referential model you can also represent hierarchies that are many levels deep: the queries then become recursive.

CTE (Common table expressions) can be used to traverse these hierarchies with ease.

This self-referential model is very similar to the "composite" design pattern.

BOM's (bill of materials) are commonly represented in this fashion.

I just wish to learn how I could achieve such a result without the use of SQL code, but I am finding it difficult to overcome this problem. I just can't escape the many-to-many relationships

I can't use boolean values since I'm using Oracle.

I'm not following you, sorry.

Could you clarify, because I fail to see what the problem is.

It's not a many to many relationship at all.

1 instance of an employee can only have one manager.

1 instance of a manager can can have 1 to many employees.

It's really no different than a "parent/child" or "has-a" relationship involving two tables: this concept of a self-referential relationship is well-established in the SQL community.

Again, it's really similar to the composite design pattern, or a bill of materials.

My problem is that I need to somehow get the EmployeeID of the manager and have it stored in each row for each employee.
Take the following tables:
Branch: BranchID City
Employee: EmployeeID Name Address TelNo Manager Branch
In the Employee table I need the Manager field to store the EmployeeID of that manager, but I also need the Branch field to relate to the BranchID field in the Branch table.
This would need me to therefore acquire data from the same table, but I want it to be able to get that data without SQL code. Perhaps the introduction of new fields, or a new table? I'm just unsure how to solve the problem

I want to achieve this without SQL code so that it could be implemented in any database type, whether it be Oracle, MySQL, MSSql, Access etc.

I'm sorry; but this makes absolutely no sense.

If you don't want to use SQL don't use a SQL database for storage.

Anyway, you can get nothing from an SQL database without using SQL, be it explicit code you wrote,
or code being written behind the scenes by the tool you are using to read it.

Relational databases are founded upon mathematical principles and you should not be
ignoring those rules without good and carefully considered reasons.

In any case, all the databases you mention above can extract the data you want using SQL.

If you want to access multiple SQL databases, you should have a common interface and instantiate the appropriate class with the appropriate optimized SQL for each database inside that class.

Indeed, if you do that properly, you can store and retrieve the data any way you want. Its up to you in the class you write to implement the common interface.

Clive

So my only option to get the Manager field in the Employee table to display the EmployeeID of that manager is by using an SQL statement like:

Such that I would introduce a Job field in the Employee table.

I think you have not picked up on the point that you do not need a separate Manager Id.

The employee table has all the info. you need if you follow that suggestion.


Note that in the (changed) second line the manager Id IS the employee Id of the manager.

If you want more information about the manager in the row then there are some special recursive SQL
syntax that can be used; but if you just need something simple then:



As MadDogBrown already explained more succinctly.

You can also turn the SQL into a view so that others can query it AS IF it was all in one table.

Clive

I did remove the Manager table :S

I understand what is being said, I just wondered all along if it were possible to achieve this result with the use of tables only, regardless of the fact that the specified SQL code may be better. However, from what I have gathered it would not be possible.

By the way is that LEFT OUTER JOIN code for Oracle? Isn't (+) used instead?

Unless you are using a seriously old version of Oracle, it now supports the standard.

I don't remember off hand when the change came.

Ah, I just read it changed after 8i