im building a database with employees and venues. i have a many to-many relation ship between the two whereby one employee can work at many venues and a venue can have many employees. now i need to introduce a linking table but am struggling what to call it or what to hold in it. any advice would be much appreciated.

well your employee and venue tables will effectively be master records. Give each a unique ID, say employee.id and venue.id. The table that links the two at its minimum will need these two ids. Anything else, like dates or whatever you will want to see out of the information can go there too. Anything specific to the emplyee or venue will go in the master record.

o ok so what iv done is created a joining table called EmployeeVenue. Iv put EmployeeID and VenueID in this table. What I want to be able to do is store what venue employees work at and also if an employee has been requested NOT to work at a venue. Would i put this information within the joining table?

well it depends on what you want from the joining table, if you want a history of who has worked where and when, say, then I'd be inclined to have another table for your restrictions.

You'd have something like:

 

EMPLOYEE

employeeID

name

.... any other fields

 

VENUE

venueID

.... any other fields

 

EMPLOYEE_VENUE

ID (primary key)

employeeID

venueID

 

With regards to Employees being request not to work at the venue, you could have another field inside EMPLOYEE_VENUE called Requested or something like that as a BIT field, 1 is yes, 0 is no...

This sounds very much like a homework question

 

Independence on your requirements you may need:

• A log table to store what venues an employee has worked at (the current venue would be saved in the employee table)
  
• A requests table to store employee venue requests - you'd have a request type column to indicate of the request has been accepted or denied
  

 

It does a bit doesn't it, especially as the follow on question seems a bit silly given the answer should be really obvious from the first solution.

 

 

 

First, with the link table (employeeVenue) you already have a list of venues that each employee works at; as that's what the table does; it links an employee to a venue; which would only have an entry if the employee works there. So you just query that table.

 

If you have three tables; employee, venue and employeeVenue:

 

employee holds a list of employees; each with a unique ID

venue holds a list of venues, each with a unique ID

employeeVenue holds a link between employees and venues

 

So is employee X (who's ID is 1) works at venue Y (which's ID is 5); then the link table will contain an entry to create this relationships:

 

employeeID: 1

venueID: 5

 

Therefore, if you wanted to know what venue an employee worked at, you'd query the employeeVenue table; something like this:

 

select venueID from employeeVenue where employeeID='1'

 

 

which would return the ID of the venues that the employee works at; there could be 1 venue or several of them if they worked at several venues (as each entry represents a unique relationship and there can be (depending on the design of your system itself) multiple entries for each employee.

 

You could make the employeeVenue table more complicated; maybe with a start and end date of employment or whatever else, if there is other required information. Your link table does not have to just contain two ID's; but with that said, you may use a 4th table for additional data (this will depend on the system itself and the data requirements).

 

 

You can also join tables within a query, so lets say the venue table contains the name of the venue, which you need (say for an on screen list):

 

SELECT employeeVenue.*, venue.* FROM employeeVenue LEFT JOIN venue ON venue.venueID = employeeVenue.venueID WHERE employeeVenue.employeeID='1'

 

 

Both queries will return the venues that an employee works at; the exact implementation (the detail) will of course depend on your systems requirements.

 

 

With your second question:

 

The link table creates a relationship between the employee and the venue; each table should contain specific data for a specific purpose. So the employeeVenue table would contain any working relationships the employee has with a venue (and vice-versa).

 

Depending on the actual requirements of the system; you could do one of two things:

 

Option 1; add a relationshipType to the employeeVenue table; so the table looks like so:

 

employeeVenue

- employeeID (the unique ID of the employee)

- venueID (the unique ID of the venue)

- relationshipType (the type of relationship that exists between the employee and the venue; i.e. employed at, restricted from, applied for position at)

 

That would mean you could have one table which contains links between lots of data and serves multiple purposes; not one purpose. Whether you would do this would depend upon the purpose of the data and it could be implemented in a different way.

 

 

Option 2; which I suspect if this is "homework" might be what your teacher is looking for:

 

Tables - changed to 4:

employee (containing employees)

venue (containing venues)

employment (formerly employee venue, containing employee-venue employment data; maybe with dates)

blacklist (for want of a better name, where employees can request NOT to work at a venue; so blacklist it from their desires; this table could also allow venues to request NOT to have certain people; maybe people who have previously been fired or were crap if it was an employment agencies database)

 

 

The tables would look like this

 

employee:

- employeeID

- any other data you store on employee's

 

venue:

- venueID

- any other data you store on venues

 

employment:

- employeeID

- venueID

- any other info you need for employment data; for example dates; start and end of employment

 

blacklist:

- employeeID

- venueID

- any other data you need; for example, instigator (who is blacklisting who, employee or venue) or reason for blacklist

 

 

That would, in terms of normalization, be a more correct way of structuring the data. Of course; I've made some assumptions about what you are doing in these examples; I think I've been clear about them.

 

 

Like zed said, you're software and data requirements will influence what direction you'll go down with your tables; and my example is just padding out what others have said.

 

 

Some rules with normalization are:

- Dont repeat data (where possible)

- Remove redundancy - empty data

- Keep data to specific purposes (don't combine data; flat tables)

- Maintain 1 to 1, 1 to many or recursive relationships; never many to many (there can be exceptions; the rule "unless you understand the rule and fully understand the consequences of breaking it, stick to the rule" applies).