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]

"john" <jo**@test.comschreef in bericht news:zP********************@casema.nl...

>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

In most cases the way you have linked the tables is a model of a many to many relationship between Message and Workgroup.
You would need a value for both message and workgroup in table Hyperlink.

In your case I think you might as well skip the table Hyperlink.
Just store the Name_Hyperlink in the tables (or maybe make 2 hyperlink tables indeed)
Just guessing because I don't know your specs...

Arno R

[john]

"Arno R" <ar***********@tiscali.nlschreef in bericht
news:45**********************@text.nova.planet.nl. ..

>In your case I think you might as well skip the table Hyperlink.
Just store the Name_Hyperlink in the tables (or maybe make 2 hyperlink
tables indeed)
Just guessing because I don't know your specs...

Thanks.
Just storing the Name_Hyperlink in the tables is not an option because one
Message can have more than one hyperlink. The same accounts for Workgroup. I
think I need to have 2 hyperlink tables, but I'd rather keep them in one
table. That's why I came up with the earlier mentioned solution.
john

[Arno R]

"john" <jo**@test.comschreef in bericht news:F5********************@casema.nl...

Thanks.
Just storing the Name_Hyperlink in the tables is not an option because one
Message can have more than one hyperlink. The same accounts for Workgroup. I
think I need to have 2 hyperlink tables, but I'd rather keep them in one
table. That's why I came up with the earlier mentioned solution.
john

After having 'consumed' this info IMO the best model is to use two hyperlink tables.
In general I think that allowing null in foreign keys is not a good idea. At least I try to avoid that.
So I do have problems with the 'no value' FK fields in your original solution...

Do a Google search on "allow null in FK" and you will find some interesting discussions and opinions about this issue.

Why the need to stick to one table ??
If needed you can use Union-query's to 'show' one 'table'.

Arno R

[Mike Gramelspacher]

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

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

Perhaps this is a three-way relationship,
Workgroups is related to Hyperlinks 1 to Many,
Messages is related to Hyperlinks 1 to Many.

Workgroups
----------
workgroup_id PRIMARY KEY
and other columns

Messages
------------
message_id PRIMARY KEY
and other columns

Hyperlinks
-----------
hyperlink_id
workgroup_id
message_id
PRIMARY KEY (hyperlink_id,
workgroup_id,
message_id)
UNIQUE (workgoup_id,
message_id)
and other fields

I have seen this kind of relationship
discussed by others.

[Mike Gramelspacher]

In article <MP************************@news.psci.net>, gr******@psci.net
says...

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

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

Perhaps this is a three-way relationship,
Workgroups is related to Hyperlinks 1 to Many,
Messages is related to Hyperlinks 1 to Many.

Workgroups
----------
workgroup_id PRIMARY KEY
and other columns

Messages
------------
message_id PRIMARY KEY
and other columns

Hyperlinks
-----------
hyperlink_id
workgroup_id
message_id
PRIMARY KEY (hyperlink_id,
workgroup_id,
message_id)
UNIQUE (workgoup_id,
message_id)
and other fields

I have seen this kind of relationship
discussed by others.

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.

[john]

"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

[Mike Gramelspacher]

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.

>

[Arno R]

"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.

OP stated he had two 1:M relationships

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

Huh ????

Arno R

[Mike Gramelspacher]

In article <45**********************@text.nova.planet.nl>,
ar***********@tiscali.nl 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.

OP stated he had two 1:M relationships

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

Huh ????

Arno R

Yes, but I inferred there could really be 3 relationships.
Maybe the inference was wrong. It happens regulary. I wonder
what entity is the recipient of these messages. A message
has a hyperlink that relates to a workgroup, but the message
itself does not. The workgroup does not need the message,
but only the hyperlink. OK.

[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.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 ProgramProcedures we have a procedure left (in ModuleProcedures) with no program...
If I delete a record in ProgramModules we have a module left (in ModuleProcedures) with no program...
If I delete a record in ModuleProcedures we have a procedure left with no module...

Also look at your table 'ProgramProcedures':
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:

TblProgramModules:
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***********@tiscali.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 ProgramProcedures we have a procedure left (in ModuleProcedures) with no program...
If I delete a record in ProgramModules we have a module left (in ModuleProcedures) with no program...
If I delete a record in ModuleProcedures we have a procedure left with no module...

Also look at your table 'ProgramProcedures':
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***********@tiscali.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 ProgramProcedures we have a procedure left (in ModuleProcedures) with no program...
If I delete a record in ProgramModules we have a module left (in ModuleProcedures) with no program...
If I delete a record in ModuleProcedures we have a procedure left with no module...

Also look at your table 'ProgramProcedures':
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

TblProgramModules:
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

TblProcedureTypes:
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***********@tiscali.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

TblProgramModules:
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***********@tiscali.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***********@tiscali.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 DatabaseCodeQuery.ModuleName
FROM DatabaseCodeQuery;

together with
SELECT Count(Distinct_module.ModuleName) AS CountOfModuleName
FROM Distinct_module;

I am referring to Multiple Modules No 1. mdb, and DatabaseCodeQuery
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