By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,953 Members | 1,131 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,953 IT Pros & Developers. It's quick & easy.

Is there any good solution to make a middle table or relation that allows usto have for each record in the Client Two or more records in the category connected with it?

P: n/a
Hi5
Hi,
I am designing a database for a client in which It has a client table
including the followings:

1-Table Client [Row_ID, Client_name, Client_full name]

2-Table lookupcategory [row_ID category_name]

3-Table Ctegory [row_ID, category_ID, client_ID]

Is there any good solution to make a middle table or relation that
allows us to have for each record in the Client Two or more records in
the category connected with it?

I know it sound stupid and it maybe against all normalisation rules but
it is in the client'srequierments.

Many thanks

Hi5

Nov 13 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Hi5 wrote:
Hi,
I am designing a database for a client in which It has a client table
including the followings:

1-Table Client [Row_ID, Client_name, Client_full name]

2-Table lookupcategory [row_ID category_name]

3-Table Ctegory [row_ID, category_ID, client_ID]

Is there any good solution to make a middle table or relation that
allows us to have for each record in the Client Two or more records in
the category connected with it?

I know it sound stupid and it maybe against all normalisation rules but
it is in the client'srequierments.


"it sounds stupid" highly depends on the point of view.

I *am* surprised by the question upon first reading, because this looks
like pretty standard design to me. And fairly normalized too.

Table 3 will function as junction table, as it is often called. Be sure
to set relationships from tables 1 and 2 to table 3, using the correct
fields (I usually name those fields the same to indicate they store the
same piece of information. row_ID in Ctegory does not tell you it is a
category ID, whereas the corresponding field in table 3 is named after
its function. Anyway)

When you set the primary key of table 3 to contain both the categoryID
and clientID, you have ensured that any combination of these can occur
in the junction table. That means any client can have some category, and
a client can have more than one category at a time.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #2

P: n/a
Bas Cost Budde <b.*********@heuvelqop.nl> wrote in
news:d9**********@localhost.localdomain:
Hi5 wrote:
Hi,
I am designing a database for a client in which It has a client
table including the followings:

1-Table Client [Row_ID, Client_name, Client_full name]

2-Table lookupcategory [row_ID category_name]

3-Table Ctegory [row_ID, category_ID, client_ID]

Is there any good solution to make a middle table or relation
that allows us to have for each record in the Client Two or more
records in the category connected with it?

I know it sound stupid and it maybe against all normalisation
rules but it is in the client'srequierments.


"it sounds stupid" highly depends on the point of view.

I *am* surprised by the question upon first reading, because this
looks like pretty standard design to me. And fairly normalized
too.

Table 3 will function as junction table, as it is often called. Be
sure to set relationships from tables 1 and 2 to table 3, using
the correct fields (I usually name those fields the same to
indicate they store the same piece of information. row_ID in
Ctegory does not tell you it is a category ID, whereas the
corresponding field in table 3 is named after its function.
Anyway)

When you set the primary key of table 3 to contain both the
categoryID and clientID, you have ensured that any combination of
these can occur in the junction table. That means any client can
have some category, and
a client can have more than one category at a time.


All true, but doesn't have anything to do with the question as I
understand it.

The answer is that there really isn't any way in a Jet database to
prevent the deletion of the last two records. With a database engine
that has triggers you could certainly do it, but not in a Jet
database.

Of course, I don't quite see how you could ever do it except with
triggers. The first trigger would be on the main table, to insert
the two required records. The second trigger would be attached to
the delete action of the join table to insure that you couldn't
delete the last two records.

But any structural schema that requires two records to exist in the
join table is going to make it difficult to add records to the main
table, since the join table can't have the records until the main
record is created, but you've got a requirement that the main record
can't exist unless it has two required records in the join table.

In Access with a Jet back end, you're going to have to program it.

This is a perfect example of a situation that would be well-handled
by having a class module that does the add, because you'd be
encapsulating the addition of records to two tables in a class that
can be treated as an object. There's not much need for multiple
instances, but it certainly makes the process easy. And, of course,
internal to the class, you'd want the whole thing wrapped in a
transaction, so that if any part of it fails, the whole thing is
rolled back.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #3

P: n/a
David W. Fenton wrote:
Bas Cost Budde <b.*********@heuvelqop.nl> wrote in
news:d9**********@localhost.localdomain:

Hi5 [OP] wrote:
[snip]
Is there any good solution to make a middle table or relation
that allows us to have for each record in the Client Two or more
records in the category connected with it?

[snip]
All true, but doesn't have anything to do with the question as I
understand it.


I appreciate your explanation here, as it confirms my beliefs about more
complex record operations (object and transaction, great tools). I still
see both possibilities for the question, as 'allows' invokes in me the
'possibility'-story more than it does the 'requirement'-story. Am
waiting for the OP to shine some light.

I presume you took the 'two' keyword as leading in the question?

Please understand that I am still trying to learn the language :-) and
there may well be something that I'm missing.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #4

P: n/a
Bas Cost Budde <b.*********@heuvelqop.nl> wrote in
news:d9**********@localhost.localdomain:
David W. Fenton wrote:
Bas Cost Budde <b.*********@heuvelqop.nl> wrote in
news:d9**********@localhost.localdomain:

Hi5 [OP] wrote:
[snip]
Is there any good solution to make a middle table or relation
that allows us to have for each record in the Client Two or more
records in the category connected with it?

[snip]
All true, but doesn't have anything to do with the question as I
understand it.


I appreciate your explanation here, as it confirms my beliefs
about more complex record operations (object and transaction,
great tools). I still see both possibilities for the question, as
'allows' invokes in me the 'possibility'-story more than it does
the 'requirement'-story. Am waiting for the OP to shine some
light.

I presume you took the 'two' keyword as leading in the question?


Well, the requirement is "two or more," which means to me that
there's a restriction where you don't want just 1 or 0 records. I
can't see any reason to word it that way.
Please understand that I am still trying to learn the language :-)
and there may well be something that I'm missing.


I'm still learning it, too. ;)

And so, it seems, is the original poster, since "allow 2 or more" is
rather ambiguous, since it implies (as I said above) that 1 or 0 is
not allowed, though it's not clear that this is a requirement.

If the poster really meant "allow 0 or more" then the question
doesn't make a lot of sense, as the data structure already works for
that. It's only if the OP wants some additional restrictions that
there arises a set of problems, the ones I described.

At least, that's the only way I can read the OP and make sense out
of it.

For one, it would be helpful if the question were in the *message*
and not just in the subject!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #5

P: n/a
"David W. Fenton" wrote
For one, it would be helpful if the
question were in the *message*
and not just in the subject!


And, for those who are relatively new in this newsgroup, there are other
good suggestions for effective use of newsgroups at
http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #6

P: n/a

Hi,

Many thanks for reading my message and trying to solve the

problem it seems to be ambigiuos therefore,let me clarify

the requierements.

Here is the tables structure:

1-Table Client [Tclient_ID, Client_name, Client_full name]

2-Table lookupcategory [Tlookupcat_ID category_name]

3-Table Ctegory [Tcat_ID, category_ID, client_ID]

I will then have a row of data in which one client has (let say) 6
categorie related to it(It may go up to all categories which exist and
are curently about 100).

To have a such data structured in MS access is there anyway of
developenig a Database?

I would be grateful to hear from you.

Kindest regards
Hi5

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #7

P: n/a
Hi 5 <fk***************@yahoo.com> wrote in
news:gn*************@news.uswest.net:
Many thanks for reading my message and trying to solve the

problem it seems to be ambigiuos therefore,let me clarify

the requierements.

Here is the tables structure:

1-Table Client [Tclient_ID, Client_name, Client_full name]

2-Table lookupcategory [Tlookupcat_ID category_name]

3-Table Ctegory [Tcat_ID, category_ID, client_ID]

I will then have a row of data in which one client has (let say) 6
categorie related to it(It may go up to all categories which exist
and are curently about 100).

To have a such data structured in MS access is there anyway of
developenig a Database?

I would be grateful to hear from you.


Your table 3 is the join table between the two tables, and, yes,
that's exactly the way to model this kind of relationship.

However, you don't actually need an Autonumber ID in your 3rd table,
and, in fact, you'd be better off without it, and just have the two
fields, category_ID and client_ID, be a compound Primary Key (which
implies a unique index). This is accomplished by highlighting both
fields in table design and clicking the KEY icon. This will set the
two fields as a compound primary key, and add a unique index on that
pair of fields.

You may also want to then add a non-unique index to the second
field.

Now, this means you will have 0 or more records in the join table.
There is no way with Access to put any more restrictions on that
table (such as limiting to 10 entries, or requiring at least 2).

If that's sufficient, your original proposal was already sufficient
to accomplish what you wanted, and that leaves me puzzled as to why
you asked a question about it, given that you'd already come to the
right answer on your own.

Perhaps there are other issues?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8

P: n/a
Hi5
Hi,
Thanks for the help, Yes there is one other issue that is the fact that
actual data comes from a very large excel sheet which was 100 columns
X 200000 rows, and the client wants to view the data in the same
format, upto here there is no problem, but it starts from where that
they have in single row 35 categories related to one client , and my
question is this is this possible with this design in MS access and if
yes how Please let me know of your opinion?

Many Thanks

Kindest regards

Nov 13 '05 #9

P: n/a
Hi5
Hi,
Thanks for the help, Yes there is one other issue that is the fact that
actual data comes from a very large excel sheet which was 100 columns
X 200000 rows, and the client wants to view the data in the same
format, upto here there is no problem, but it starts from where that
they have in single row 35 categories related to one client , and my
question is this is this possible with this design in MS access and if
yes how Please let me know of your opinion?

Many Thanks

Kindest regards

Nov 13 '05 #10

P: n/a
"Hi5" <fk***************@yahoo.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:
Thanks for the help, Yes there is one other issue that is the fact
that
actual data comes from a very large excel sheet which was 100
columns
X 200000 rows, and the client wants to view the data in the same
format, upto here there is no problem, but it starts from where
that they have in single row 35 categories related to one client ,
and my question is this is this possible with this design in MS
access and if yes how Please let me know of your opinion?


Yes, you can create a query with all 3 tables in it and drag all the
the fields in the two tables that are joined (i.e., omitting the
join table) into the QBE grid. The result will be something that
looks just like a spreadsheet where all the data in the main table
repeats.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #11

P: n/a
Hi5
Hi,
If the structure is O.K and evry thing else is allright it doesn't
work?
I mean I have a client who has five categories how to record these in
the databes?
Many Thabks

Nov 13 '05 #12

P: n/a
Hi5
Hi,
I am new to access I used to do DBs in Oracle and Mysql,I would be
grateful if someone could translate the following words for me
What is QBE grid?
How to do that?
Many Thanks

Nov 13 '05 #13

P: n/a
> I am new to access I used to do DBs in Oracle and Mysql,I would be
grateful if someone could translate the following words for me
What is QBE grid?
Query By Example, the basic design view for a query.
How to do that?


Select a query object in the database window and click the button
Design, or choose the equivalent command from the menu. Or, press
Ctrl-Enter on the keyboard (my preference)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #14

P: n/a
"Hi5" <fk***************@yahoo.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:
If the structure is O.K and evry thing else is allright it doesn't
work?
I mean I have a client who has five categories how to record these
in the databes?


I don't know the answer to your question because you haven't given
any clues as to what, exactly, isn't working.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #15

P: n/a
Hi5
Dear Dave,

You are absolutely right, mate!

In this matter the only logical solution would be as you stated to use
and link an object class ,Or linked list to each field in the middle
table ,Anyway I could not find any logical solution in access so, I
used a stupid one which is to make the middle table as big as the most
number of data existed + a number wich will cover all possilities.

Many Thanks

Nov 13 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.