Path: news.easynews.com!easynews!sn-xit-02!supernews.com!postnews1.google.com!not-for-mail From: mespadoto@yahoo.com (Mateus Espadoto) Newsgroups: comp.databases.theory Subject: Re: which database design should I use ? Date: 26 Dec 2001 02:01:14 -0800 Organization: http://groups.google.com/ Lines: 62 Message-ID: <8ca34365.0112260201.20c5497@posting.google.com> References: NNTP-Posting-Host: 200.197.161.162 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1009360874 19205 127.0.0.1 (26 Dec 2001 10:01:14 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 26 Dec 2001 10:01:14 GMT Xref: easynews comp.databases.theory:19325 X-Received-Date: Wed, 26 Dec 2001 03:06:55 MST (news.easynews.com) Try a self relationship in PROJECT CREATE TABLE PROJECT ( ProjectNumber INT PRIMARY KEY, SubProjectNumber INT FOREIGN KEY (SubProjectNumber) REFERENCES PROJECT (ProjectNumber)) CREATE TABLE ORDERS ( OrderNumber INT PRIMARY KEY, ProjectNumber INT FOREIGN KEY (ProjectNumber) REFERENCES PROJECT (ProjectNumber)) Now you can have several levels of subprojects, and can reference the orders by any project number. Actually, it doesn't matter if it's a main or subproject: all of them will exist as "main projects" until you reference them in the subproject field, wich will cause them to turn into subprojects. Good Luck! Mateus Espadoto chris.gheysen@village.uunet.be (chris gheysen) wrote in message news:... > Hi, > > I am having the following problem : > > After having created a general ORDER-table in my database, I should have the > opportunity to relate each order to a certain project. > The obvious solution would be to create a PROJECT-table with a key-field > Projectnumber and to add a field to my ORDER-table that links to the > PROJECT-table : > > ORDER > Ordernumber (Primary key) > Projectnumber (Foreign key to projectnumber in PROJECT) > ... > > PROJECT > Projectnumber (Primary Key) > ... > > > However, there is a possibility that a certain project resides in another > project. There can be several levels of sub-projects : > > e.g. : - Myproject > - MySubproject > - MySubSubproject > - MySubSubSubproject > - ... > > There should be a possibility to link each order to one or more of those > projects or subprojects. > > Can anyone help me how the database design should look like ? > > Thanks in advance, > > Chris Gheysen