How could I design a database that

[jennyek2000]

How could I design a database that has a number of tables that are created each time a user creates a new project i.e. duplicate tables with different data.

 

[SQLBI]

Jenny,

If your going to follow relational database theory, you don't have to.

You Simply need a project table, with relationships to other tables in your db.

For example,

You have many projects, all with the same attributes such as StartDate, EndDate, ProjectManager, Customer.

Your tables would look like this;

Project:
ProjectID
ProjectName
StartDate
EndDate
ProjectManager
Customer

Customer:
CustomerID
CustomerName
Address1
Address2
City
State/Province
Tel

ProjectManager:
ManagerID
ManagerName
Department

Your relationships would be:

Project.Customer = Customer.CustomerID
Project.ProjectManager = ProjectManager.ManagerID

So a Customer may have many projects, but a Project is for exactly 1 customer.

And a Project Manager may manage many projects, but a project has only 1 manager.

Hope this helps.






Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[jennyek2000]

In the system you have a number of projects, each with there own attributes, which are stored in a table. Each project consists of a number of documents, which must also be stored in a table. So for every record in the projects table there is a table of documents. The system must allow you to create a job, which in turn creates a new document table with new data. How could I do this?

 

[JeremyNYC]

Jenny,

No, actually, you're wrong. There is no need to create a new table. Just add a field to your table to differentiate between different projects. Then you can filter based on that field.

You should study up some on relational database theory. There's an excellent article written by Paul Litwin up on my website. Read that. Then come back to your problem.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.