Hello People.
I do not know whether any of you have had a situation like this before.
Here it is.
I have 3 tables
Party,Employee and Salesman
Now in all these 3 tables I have some common fields.
Code,Name,Address,Tel,Fax,Mobile
In the Party table other than the above I have
Type,CreditLimit,ShippingAddress
Similarly in the Employee Table I have
Salary,Picture,Nationality,PassportNo,PassportExpiryDate
In the Salesman table I have
ComissionPercentage
Since VFP is an object oriented language I wanted to be able to have Inheritance
in tables.
This is because my system has a option for making payments. The user can make
a payment to either of the 3.
Normally if I have 3 tables I would do a select union on the common fields.
But then I had a problem. What if any of the 3 tables had a common code. So
I decided to make a common table called People with all the fields but
then a lot of space was getting wasted in the table. Like for example a Party
record will never fill the the PassportNo field and similarly a SalesMan
record will never have a ShippingAddress.
I thought about it and came up with this Idea.
I made a table called Base with all the common fields + a ID field
Code,Name,Address,Tel,Fax,Mobile,ID
ID is the PrimaryKey
Then I made a table called PartyDet like this
Type,CreditLimit,ShippingAddress,LinkId
Then I created a view like this.
Create SQL View Party as
Select * FROM Base RIGHT OUTER JOIN PartyDet ;
ON Base.id = Party.linkid
Then I made a stored Procedure in the DBC to generate a unique value for
ID
Then I set the Default property in the view for ID as NewID()
and of LinkId as ID
What this will do is when ever I append a record in the view it will make sure
LinkID and ID are the same.
We need this to keep the Base table and the PartyDet table linked.
Also I marked the view as updateable and set the Keys to ID and LinkID
and checked the send SQL updates.
Now I have a view with all the necessary fields.
I did the same for the other 2 tables.
All is working as expected. All appropriate fields also get updated.
Now I wanted to ask one thing. Is there any built in facility to do so automatically
or do I always have to go through the long procedure of creating the view.
Also am I doing something wrong conceptually ?
Is there a better way to do the same ?
