×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to perform INSERT when using subtype/supertype structure?

How to perform INSERT when using subtype/supertype structure?

How to perform INSERT when using subtype/supertype structure?

(OP)
Hi all.

I'm new to this forum, and a newbie to database programming generally.

I'm trying to create a database to track clients using MS Access 2000. At the moment, I've decided to implement a supertype/subtype structure, with the following table schema:

Clients Table (the supertype table):
ClientID (pk) - autonumber
ClientType - stores values 1 for "Individual" and 2 for "Company". These values identify the relevant subtype table as outlined below.
Address
Suburb
State
PostCode
Country
Tel
Fax
Email

CompanyClients
ClientID (fk) - integer - references ClientID in Clients Table
CompanyName
CompanyNumber

IndividualClients
ClientID (fk) - integer - references ClientID in Clients Table
Title
FirstName
MiddleName
Surname
Suffix

Obviously I've chosen this schema because there are attributes that apply generally to clients (like Address etc) and other attributes that apply only to specific types of clients (like CompanyNumber).

Having modeled and implemented this schema though, I'm stuck in terms of how to perform INSERTS; ie, say I have a new new corporate client named Smith Enterprises Pty Ltd with a company number ACN 123 456 789 which has its offices at 1 River Road, Brisbane, Qld 4122 (Australia). In order to record this new company's details I obviously need to create a new entry in the Clients Table first so as to generate a new ClientID (and inserting the address details while I'm at it), and then use that ClientID to create a new record in the CompanyClients subtype table. Could someone please give me an example of how I would structure the required INSERT sql command?

In case its relevant, I'm planning to enter the client details via a vb.net windows form. The form will contain a drop-down list which will enable the user to select the relevant ClientType.

Thanks in advance
Robert

RE: How to perform INSERT when using subtype/supertype structure?

I was sure I'd posted an answer this morning but it's obviously gone walkabout.

Access doesn't (so far as I know) support supertypes and subtypes. You'll have to fake it by having a single structure like this:

CODE

1 ClientID (fk) - integer
2 CompanyName
3 CompanyNumber
4 Title
5 FirstName
6 MiddleName
7 Surname
8 Suffix
Fields 2 and 3 would be blank for private clients, fields 4 to 8 would be blank for companies.

Geoff Franklin
www.alvechurchdata.co.uk

RE: How to perform INSERT when using subtype/supertype structure?

(OP)
Thanks Geoff.

Rob.

RE: How to perform INSERT when using subtype/supertype structure?

if the database can support tables, then the database can support subtypes and supertypes, so yes, access can support subtypes and supertypes, because all they are is tables

robert, your INSERT for the subtype row after inserting a new supertype row is exactly as you would expect it to be, except that the value you use for the FK has to be derived from the INSERT statement of the supertype

i'm pretty sure you can use @@IDENTITY in access, otherwise you could query back the supertype row after insertion using the values of the other columns that uniquely identify it

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

RE: How to perform INSERT when using subtype/supertype structure?

Quote:

access can support subtypes and supertypes

I can work with subtypes and supertypes as concepts at design time but I didn't realise that I could subclass an Access table named "client" into "personal" and "corporate" subtypes.
Could you give us some more details?

Geoff Franklin
www.alvechurchdata.co.uk

RE: How to perform INSERT when using subtype/supertype structure?

more details?  they're just ordinary tables

client is a table (typically with an autonumber PK)

personal is a table, with a PK that is also a foreign key to client

corporate is a table, with a PK that is also a foreign key to client

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

RE: How to perform INSERT when using subtype/supertype structure?

Quote:

they're just ordinary tables

I guess I've been reading too much into the word "subtype".

Some databases allow you to create a "client" table and define some fields and all its relationships. Then you could subclass this table definition into "personal" and "corporate" and these two new tables would inherit the fields and relations defined for their supertype. In Access I'd do it in the way you describe.

Geoff Franklin
www.alvechurchdata.co.uk

RE: How to perform INSERT when using subtype/supertype structure?

i'd like to know which databases allow tables to "inherit" fields and relations

just a curiosity, i'm not eagerly looking for this feature, and i doubt i would use that database anyhow, but i sure would like to know

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

RE: How to perform INSERT when using subtype/supertype structure?

Quote:

i'd like to know which databases allow tables to "inherit" fields and relations
Me too<g>. We covered the concept in college many years ago but I'd never seen one in real life.

Geoff Franklin
www.alvechurchdata.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close