Design question

[john]

I have 3 tables: Message, Workgroup, and Hyperlink. Message has 1xM link
with Hyperlink and Workgroup has 1xM link with Hyperlink. Hyperlink has the
following fields:
IDhyperlink*
IDmessage
IDworkgroup
Name_Hyperlink
In most cases either IDmessage or IDworkgroup will have no value.
Is this a good approach or should I make 2 Hyperlink tables or...?
thanks,
john

[Arno R]

"Mike Gramelspacher" <gr******@psci. netschreef in bericht news:MP******** *************** *@news.psci.net ...

In article <W5************ ********@casema .nl>, jo**@test.com says...

>"Mike Gramelspacher" <gr******@psci. netschreef in bericht
news:MP******* *************** **@news.psci.ne t...

>>

My previous example should not have the UNIQUE statement,
but it may be that you have 3 many-to-many relationships.

Message-Workgroup is M:M, Message-Hyperlink is M:M and
Hyperlink-Workgroup is M:M.

You have 3 tables and need 3 relationships to resolve them,
so, you need 6 tables.

Thanks. I'll look into that.
john

Here is a small database where I tried to do multiple relations.
http://www.psci.net/gramelsp/temp/Mu...s%20No%202.zip

This was just a learning thing, so take it for what it is worth.

Sorry Mike,
I looked at the zip but that design is just not right at all...
No offence meant, but I take it for learning indeed.

A few comments:
Your table 'Modules' is a single-field-table with a PK on the name 'Modules'.
What if a module with the same name exist in another program ??

Your table 'Procedures' (also single-field) has a PK on the name 'Procedures'.
What if a procedure with the same name exists in another module in another program ??

If I delete a record in ProgramProcedur es we have a procedure left (in ModuleProcedure s) with no program...
If I delete a record in ProgramModules we have a module left (in ModuleProcedure s) with no program...
If I delete a record in ModuleProcedure s we have a procedure left with no module...

Also look at your table 'ProgramProcedu res':
You are (only) storing ProcedureNames together with ProgramNames here.
I guess that procedures 'belong to' modules, and modules 'belong to' programs.
Thus you could store ProcedureName (and author and Type) together with a ModuleProgramID

- - - - -

You are only storing 5 attributes and you are using 8 tables... Why ??
In fact I think you could do with only two tables instead of 8:

TblProgramModul es:
IDProgMod PK
Program
Module Unique index here on Program together with Module

TblProcedures:
IDProgMod FK
ProcedureName Unique index here on ProcedureName together with IDProgMod
ProcedureAuthor
ProcedureType

Arno R

[Mike Gramelspacher]

In article <45************ **********@text .nova.planet.nl >,
ar***********@t iscali.nl says...

>
"Mike Gramelspacher" <gr******@psci. netschreef in bericht news:MP******** *************** *@news.psci.net ...

In article <W5************ ********@casema .nl>, jo**@test.com says...

"Mike Gramelspacher" <gr******@psci. netschreef in bericht
news:MP******** *************** *@news.psci.net ...
My previous example should not have the UNIQUE statement,
but it may be that you have 3 many-to-many relationships.

Message-Workgroup is M:M, Message-Hyperlink is M:M and
Hyperlink-Workgroup is M:M.

You have 3 tables and need 3 relationships to resolve them,
so, you need 6 tables.

Thanks. I'll look into that.
john

Here is a small database where I tried to do multiple relations.
http://www.psci.net/gramelsp/temp/Mu...s%20No%202.zip

This was just a learning thing, so take it for what it is worth.

Sorry Mike,
I looked at the zip but that design is just not right at all...
No offence meant, but I take it for learning indeed.

A few comments:
Your table 'Modules' is a single-field-table with a PK on the name 'Modules'.
What if a module with the same name exist in another program ??

Your table 'Procedures' (also single-field) has a PK on the name 'Procedures'.
What if a procedure with the same name exists in another module in another program ??

If I delete a record in ProgramProcedur es we have a procedure left (in ModuleProcedure s) with no program...
If I delete a record in ProgramModules we have a module left (in ModuleProcedure s) with no program...
If I delete a record in ModuleProcedure s we have a procedure left with no module...

Also look at your table 'ProgramProcedu res':
You are (only) storing ProcedureNames together with ProgramNames here.
I guess that procedures 'belong to' modules, and modules 'belong to' programs.
Thus you could store ProcedureName (and author and Type) together with a ModuleProgramID

- - - - -

You are only storing 5 attributes and you are using 8 tables... Why ??
In fact I think you could do with only two tables instead of 8:

Indeed it was a learning attempt. I will look closely at all you have
written. It may be that you have done me a valuable service and that is
appreciated. Thanks.

[Mike Gramelspacher]

In article <45************ **********@text .nova.planet.nl >,
ar***********@t iscali.nl says...

>
"Mike Gramelspacher" <gr******@psci. netschreef in bericht news:MP******** *************** *@news.psci.net ...

In article <W5************ ********@casema .nl>, jo**@test.com says...

"Mike Gramelspacher" <gr******@psci. netschreef in bericht
news:MP******** *************** *@news.psci.net ...
My previous example should not have the UNIQUE statement,
but it may be that you have 3 many-to-many relationships.

Message-Workgroup is M:M, Message-Hyperlink is M:M and
Hyperlink-Workgroup is M:M.

You have 3 tables and need 3 relationships to resolve them,
so, you need 6 tables.

Thanks. I'll look into that.
john

Here is a small database where I tried to do multiple relations.
http://www.psci.net/gramelsp/temp/Mu...s%20No%202.zip

This was just a learning thing, so take it for what it is worth.

Sorry Mike,
I looked at the zip but that design is just not right at all...
No offence meant, but I take it for learning indeed.

A few comments:
Your table 'Modules' is a single-field-table with a PK on the name 'Modules'.
What if a module with the same name exist in another program ??

Your table 'Procedures' (also single-field) has a PK on the name 'Procedures'.
What if a procedure with the same name exists in another module in another program ??

If I delete a record in ProgramProcedur es we have a procedure left (in ModuleProcedure s) with no program...
If I delete a record in ProgramModules we have a module left (in ModuleProcedure s) with no program...
If I delete a record in ModuleProcedure s we have a procedure left with no module...

Also look at your table 'ProgramProcedu res':
You are (only) storing ProcedureNames together with ProgramNames here.
I guess that procedures 'belong to' modules, and modules 'belong to' programs.
Thus you could store ProcedureName (and author and Type) together with a ModuleProgramID

- - - - -

You are only storing 5 attributes and you are using 8 tables... Why ??
In fact I think you could do with only two tables instead of 8:

I have looked at some issues.
Yes, a module with the same name can exist in another program, and a
procedure with the same name can exist in another module in another
program. True situations.

I can end up with orphaned procedures, but I do not think it matters.

Of the 8 tables 3 are relationships. I think this is as it should be.

Authors and Types are lookup tables and limit the possibilities to what
is in the lookup tables.

The problems that I see are the cascading deletes. Deleting an entity
may have consequences that are not wanted. I really need to look at
deletes more. I eliminated the cascading deletes.

[Arno R]

"Mike Gramelspacher" <gr******@psci. netschreef in bericht news:MP******** *************** *@news.psci.net ...

I have looked at some issues.

I also looked a bit better ... ;-(
My solution still needs some normalizing!!

I was a bit eager skipping some tables, but we need at least 3NF isn't it ??
So 'Programs' should be a separate table and Authors is also a candidate for a separate table.
I will give you the revised table-structure at the end.

Yes, a module with the same name can exist in another program, and a
procedure with the same name can exist in another module in another
program. True situations.

I can end up with orphaned procedures, but I do not think it matters.

Of course that matters !!

Of the 8 tables 3 are relationships. I think this is as it should be.

I disagree

Authors and Types are lookup tables and limit the possibilities to what
is in the lookup tables.

Authors is a candidate indeed as I said above. (My first solution was incomplete)
Because there are only two possible Types I don't think you *need* a separate table for Types
(I would just limit the input with a combo)
But in a 'strict' sense, yes this could be a separate table, so let's add that table as well.

The problems that I see are the cascading deletes. Deleting an entity
may have consequences that are not wanted. I really need to look at
deletes more. I eliminated the cascading deletes.

You should indeed avoid that. Cascading deletes can be very dangerous.

New structure:

TblPrograms:
ProgramID (PK)
ProgramName

TblProgramModul es:
IDProgMod (PK)
ProgramID (FK)
ModuleName Unique index here on ProgramID together with ModuleName

TblProcedures:
ProcedureID (PK)
IDProgMod (FK)
ProcedureName Unique index here on ProcedureName together with IDProgMod
AuthorID (FK)
ProcTypeID (FK)

TblAuthors:
AuthorID (PK)
AuthorName

TblProcedureTyp es:
ProcTypeID (PK)
ProcTypeName
Look at http://home.tiscali.nl/arracom/design.jpg too see the difference between the structures.

Arno R

[Mike Gramelspacher]

In article <45************ **********@text .nova.planet.nl >,
ar***********@t iscali.nl says...

>
"Mike Gramelspacher" <gr******@psci. netschreef in bericht news:MP******** *************** *@news.psci.net ...

I have looked at some issues.

I also looked a bit better ... ;-(
My solution still needs some normalizing!!

I was a bit eager skipping some tables, but we need at least 3NF isn't it ??
So 'Programs' should be a separate table and Authors is also a candidate for a separate table.
I will give you the revised table-structure at the end.

Yes, a module with the same name can exist in another program, and a
procedure with the same name can exist in another module in another
program. True situations.

I can end up with orphaned procedures, but I do not think it matters.

Of course that matters !!

Of the 8 tables 3 are relationships. I think this is as it should be.

I disagree

Authors and Types are lookup tables and limit the possibilities to what
is in the lookup tables.

Authors is a candidate indeed as I said above. (My first solution was incomplete)
Because there are only two possible Types I don't think you *need* a separate table for Types
(I would just limit the input with a combo)
But in a 'strict' sense, yes this could be a separate table, so let's add that table as well.

The problems that I see are the cascading deletes. Deleting an entity
may have consequences that are not wanted. I really need to look at
deletes more. I eliminated the cascading deletes.

You should indeed avoid that. Cascading deletes can be very dangerous.

New structure:

TblPrograms:
ProgramID (PK)
ProgramName

TblProgramModul es:
IDProgMod (PK)
ProgramID (FK)
ModuleName Unique index here on ProgramID together with ModuleName

TblProcedures:

This seems to be a correct solution, but it is not my solution.
http://www.psci.net/gramelsp/temp/Mu...ships_No_1.zip

In your solution can you have a program with two modules, which
contain the same procedures?

I am unsure about dangling procedures. I sort of look at the three
entity tables as lookup tables which contain all programs, modules and
procedures which exist in my programs. I compare this to a State_code
lookup table with all U.S. States and Possessions. Certainly I will
never use each and every code.

I agree that function and subprogram could be a value list in a combo
box. That is how I would do it now, although a separate table is not
incorrect.

[Arno R]

"Mike Gramelspacher" <gr******@psci. netschreef in bericht news:MP******** *************** *@news.psci.net ...

This seems to be a correct solution, but it is not my solution.
http://www.psci.net/gramelsp/temp/Mu...ships_No_1.zip

Seems all right at first sight, but it is *not* a correct solution...
It is MUCH better than the other one but still wrong.

The Modules table is the problem here.
There is a relation (1:M) between Programs and Modules. We don't see that relation here.

What if we might like to simply count the *exact* number of modules ??
We can't do this reliably because we can have modules with the same name...
Or what if we want to know in what Program the module "XXX" exists ??
If we delete the procedures belonging to that module the 'link' is lost!!
That is only because the structure is wrong!!

In my solution we do not have these problems. It is more 'bullet-proof'.
Also the relationships are more 'clear' (at least the way I look at them)

In your solution can you have a program with two modules, which
contain the same procedures?

No, but since Access will not allow that, I did not allow that.
I said: "Unique index here on ProcedureName together with IDProgMod"
This index is meant to prevent just that possibility.

If we need that possibility we can do so by changing or removing that specific index in TblProcedures.
We could allow duplicates for ProcedureName. (but why ???)

I am unsure about dangling procedures. I sort of look at the three
entity tables as lookup tables which contain all programs, modules and
procedures which exist in my programs. I compare this to a State_code
lookup table with all U.S. States and Possessions. Certainly I will
never use each and every code.

If you want to 'get it right' you will have to learn to look differently...

You can *not* compare your tables with a Lookup table like the State_Codes.
But I guess I simply don't understand what you are trying to say here...

I agree that function and subprogram could be a value list in a combo
box. That is how I would do it now, although a separate table is not
incorrect.

I agree on this,

Arno R

[Mike Gramelspacher]

In article <45************ **********@text .nova.planet.nl >,
ar***********@t iscali.nl says...

>
"Mike Gramelspacher" <gr******@psci. netschreef in bericht news:MP******** *************** *@news.psci.net ...

This seems to be a correct solution, but it is not my solution.
http://www.psci.net/gramelsp/temp/Mu...ships_No_1.zip

Seems all right at first sight, but it is *not* a correct solution...
It is MUCH better than the other one but still wrong.

The Modules table is the problem here.
There is a relation (1:M) between Programs and Modules. We don't see that relation here.

What if we might like to simply count the *exact* number of modules ??
We can't do this reliably because we can have modules with the same name...
Or what if we want to know in what Program the module "XXX" exists ??
If we delete the procedures belonging to that module the 'link' is lost!!
That is only because the structure is wrong!!

In my solution we do not have these problems. It is more 'bullet-proof'.
Also the relationships are more 'clear' (at least the way I look at them)

In your solution can you have a program with two modules, which
contain the same procedures?

No, but since Access will not allow that, I did not allow that.
I said: "Unique index here on ProcedureName together with IDProgMod"
This index is meant to prevent just that possibility.

If we need that possibility we can do so by changing or removing that specific index in TblProcedures.
We could allow duplicates for ProcedureName. (but why ???)

I am unsure about dangling procedures. I sort of look at the three
entity tables as lookup tables which contain all programs, modules and
procedures which exist in my programs. I compare this to a State_code
lookup table with all U.S. States and Possessions. Certainly I will
never use each and every code.

If you want to 'get it right' you will have to learn to look differently...

You can *not* compare your tables with a Lookup table like the State_Codes.
But I guess I simply don't understand what you are trying to say here...

I agree that function and subprogram could be a value list in a combo
box. That is how I would do it now, although a separate table is not
incorrect.

I agree on this,

Arno R

Is the second example equivalent to your design?
http://www.psci.net/gramelsp/temp/Relationships.jpg

I will work with both designs, but I do not have the time to devote to
it now. I will get back in the not too distant future, but I want to
make working databases using both designs.

With the other design it was still possible to get a count of modules,
but not as easily as with your design.

I wanted to get all my program names, modules names and procedure names
into tables and then build the relationships. The way I was thinking
the entities would only be lookup tables.

Anyway, I cannot really do anything now. I sincerely appreciate you
kind help. Your design looks to be the correct one. Thanks.

[Arno R]

"Mike Gramelspacher" <gr******@psci. netschreef in bericht news:MP******** *************** *@news.psci.net ...

Is the second example equivalent to your design?
http://www.psci.net/gramelsp/temp/Relationships.jpg

Yes, the 'bottem' design is equivalent. This design is all right as far as I can see now.
The main difference I notice is in the ID of the Programs-Modules table.
Some people like a PK which is single RecordID, more than a composite PK.
(It's more easy when we need to get a specific record)

I will work with both designs, but I do not have the time to devote to
it now. I will get back in the not too distant future, but I want to
make working databases using both designs.

==>I would use the second design, because the other one is NOT right...

With the other design it was still possible to get a count of modules,
but not as easily as with your design.

You can NOT get a *reliable* count of modules. Also you could get orphan modules.
(Sorry if I am nitpicking here)

I wanted to get all my program names, modules names and procedure names
into tables and then build the relationships. The way I was thinking
the entities would only be lookup tables.

Anyway, I cannot really do anything now. I sincerely appreciate you
kind help. Your design looks to be the correct one. Thanks.

You are welcome

Arno R

[Mike Gramelspacher]

In article <45************ **********@text .nova.planet.nl >,
ar***********@t iscali.nl says...

>
"Mike Gramelspacher" <gr******@psci. netschreef in bericht news:MP******** *************** *@news.psci.net ...

Is the second example equivalent to your design?
http://www.psci.net/gramelsp/temp/Relationships.jpg

Yes, the 'bottem' design is equivalent. This design is all right as far as I can see now.
The main difference I notice is in the ID of the Programs-Modules table.
Some people like a PK which is single RecordID, more than a composite PK.
(It's more easy when we need to get a specific record)

I will work with both designs, but I do not have the time to devote to
it now. I will get back in the not too distant future, but I want to
make working databases using both designs.

==>I would use the second design, because the other one is NOT right...

With the other design it was still possible to get a count of modules,
but not as easily as with your design.

You can NOT get a *reliable* count of modules. Also you could get orphan modules.
(Sorry if I am nitpicking here)

I wanted to get all my program names, modules names and procedure names
into tables and then build the relationships. The way I was thinking
the entities would only be lookup tables.

Anyway, I cannot really do anything now. I sincerely appreciate you
kind help. Your design looks to be the correct one. Thanks.

You are welcome

Arno R

Number of modules is 8, but number of modules in relationships is 4.
I will double check, but

Query: Distinct_module
SELECT DISTINCT DatabaseCodeQue ry.ModuleName
FROM DatabaseCodeQue ry;

together with
SELECT Count(Distinct_ module.ModuleNa me) AS CountOfModuleNa me
FROM Distinct_module ;

I am referring to Multiple Modules No 1. mdb, and DatabaseCodeQue ry
already is there.

It seems to work.

The main thing I need to model is than the same module name can be in
different programs, but have different procedures. That is the crux of
the problem.

Mike