quote:

---

So I end up with this scenario of having a job which is a record in EMS_Main, which needs to have multiple tables attached to it somehow. So what's the best/recommended way to do this? I was toying with the idea of creating a separate database for each job (we quote for ~1500 jobs per year, so that's a lot of databases and tables), and just changing the connection string in the code, but that sounds excessive, possibly a recipe for pain later on, and I don't even know if it's possible (I'm guessing there's an upper limit on number of databases). Is there a nice, easy way to organise this within EMS_Main (can tables be put into folders or classes or something? So I could refer to them like EMS_Main.[folder].[table].[field name]), or am I barking up the wrong tree?

---

select
  V.FieldName
from
  EMS_Main E, Venues V
where
  E.JobID = 3764 and
  V.VenueID = E.VenueID

quote:

---

Originally posted by AxeSlash

I think you're misunderstanding me (I've probably worded my post badly!)

I guess what I'm really asking is how best to store the specification for each job without it getting messy.

I've done a bit more thinking, and the only way I can see is to create two tables for each job:
- Specification
- Rest of job info (client details, venue details, payment details, dates etc etc)

I'd like the two to stay together in a folder (is that possible?), within a separate area (presumably a separate database) from EMS_Main.

So I guess I'd want e.g. EMS_Jobs.[job number].Specification as a table and EMS_Jobs.[job number].Details as a table, where [job number] is like a folder or something?

This is the only way I can see to do it at the moment - a) is this possible, and/or b)is there a better way?

---