How to perform INSERT when using subtype/supertype structure?

[RobertLanders]

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

 

[alvechurchdata]

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:

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

http://www.alvechurchdata.co.uk

 

[RobertLanders]

Thanks Geoff.

Rob.

 

[r937]

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)

 

[alvechurchdata]

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

http://www.alvechurchdata.co.uk

 

[r937]

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)

 

[alvechurchdata]

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

http://www.alvechurchdata.co.uk

 

[r937]

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)

 

[alvechurchdata]

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

http://www.alvechurchdata.co.uk