How do you implement subtype tablewith attribute inheritance in ac

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to implement library for loanable items. A rental item table exists
which contains the bulk of the attributes but there are subtypes of this
being DVD, video and computer game which each contain specialized
information. When I add a DVD for example I want to add all the attributes
inherited from item plus the specialized attributes in DVD e.g. director, DVD
release. How can I do this in access?
 
Hi, smit.

Assuming you have a table that contains this specialized information, you
already have access to it via a query. It IS NOT advisable to try to store
this supplemental information redundantly in your Rental table. It is
already stored in one authoritative place that you have Access to. (pun
intended)

To *display* this information on your form, or in a report, etc., include
these fields in a query, linking on the ProductID, and place controls bound
to these fields on your form and/or report.

Hope that helps.
Sprinks
 
smit127 said:
I want to implement library for loanable items. A rental item table exists
which contains the bulk of the attributes but there are subtypes of this
being DVD, video and computer game which each contain specialized
information. When I add a DVD for example I want to add all the attributes
inherited from item plus the specialized attributes in DVD e.g. director, DVD
release.
Click to expand...

Do you mean something like this:

CREATE TABLE RentalItems (
rental_item_code VARCHAR(9) NOT NULL,
CHECK (LEN(rental_item_code) = 9),
rental_item_type VARCHAR(35) NOT NULL,
CHECK (rental_item_type IN ('DVD', 'Video', 'Computer game')),
PRIMARY KEY (rental_item_type, rental_item_code),
<<generic attribute columns here>>
)
;
CREATE TABLE DVDs (
rental_item_code VARCHAR(9) NOT NULL,
rental_item_type VARCHAR(35) NOT NULL,
CHECK (rental_item_type = 'DVD'),
PRIMARY KEY (rental_item_type, rental_item_code),
FOREIGN KEY (rental_item_code, rental_item_type)
REFERENCES RentalItems (rental_item_type, rental_item_code)
ON UPDATE CASCADE ACTION ON DELETE CASCADE,
<<DVD-specific attribute columns here>>
)
;

And a similar table for each of Video, Computer game etc.

Of course, next you will want a Films table:

CREATE TABLE Films (
rental_item_code VARCHAR(9) NOT NULL,
rental_item_type VARCHAR(35) NOT NULL,
CHECK (rental_item_type IN ('DVD', 'Video')),
PRIMARY KEY (rental_item_type, rental_item_code),
FOREIGN KEY (rental_item_code, rental_item_type)
REFERENCES RentalItems (rental_item_type, rental_item_code)
ON UPDATE CASCADE ACTION ON DELETE CASCADE,
<<Film-specific attribute columns here>>
)
;
How can I do this in access?
Click to expand...

To be able to use the above syntax, you need to either use Access2003
in 'ANSI-92' mode or use ADO in earlier versions e.g.

CurrentProject.Connection.Execute "<<SQL code here>>"

Jamie.

--
 
Back
Top