setting relationships at runtime

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

Guest

Hi,

Relationships are normally set at design time, it is possible to set it at
runtime, so that the user can select from a form which two tables to link,
with what fields each time the database is opened or used?

Thanks,
 
CathyZ said:
Relationships are normally set at design time, it is possible to set it at
runtime
Click to expand...

Are you building a wizard? It's very uncommon for the user to set up
relationships between tables, unless it's a wizard that does something like
allow a user to import temp tables and use ETL to normalize the data, where
the designer of the wizard doesn't know the name of the imported tables or
their column names at design time.

It's very easy to do. You just need to add a constraint for the temp two
tables, then drop the constraint when finished. Both tables must be in the
current database. The syntax for creating the constraint is like this:

ALTER TABLE tblTempChild
ADD CONSTRAINT tblTempChild_tblTempParent_FK
FOREIGN KEY (ChildColumnName)
REFERENCES tblTempParent (ParentColumnName);

To drop the constraint use syntax like this:

ALTER TABLE tblTempChild
DROP CONSTRAINT tblTempChild_tblTempParent_FK;
 
Thanks, I haven't thought about a wizard, and haven't created one before, but
I will look into it and use the constraints and send feedback as to how it
worked out. I need it because I worked on a project for a farmers group,
which has one master list of farmers, however, now they get a monthly current
list of farmers and want to link these two tables to deal with only the
monthly current farmers, and I want them to be able to select whatever month
they are dealing with and get it linked to the master list.
 
I would think you'd have a Start and End date in the table for each member,
and then run a query that selects only those where the desired month is
greater than or equal to the Start date and less than or equal to the End
date.
 
CathyZ said:
however, now they get a monthly current
list of farmers and want to link these two tables to deal with only the
monthly current farmers, and I want them to be able to select whatever month
they are dealing with and get it linked to the master list.
Click to expand...

All you can do is cross your fingers that it'll be done correctly if you give
the users a tool that alters the structure of the database. You have a few
alternatives.

If you need to keep a history of these monthly farmers, then the users can
append each new list monthly into the same (permanent child) table, but add
two columns, one for the current month and one for the current year. If you
don't need to keep a history, when it comes time to import the current list,
delete the records from last month and compact the database, then import the
new list. You wouldn't need a current month and year, because it would be
understood that if there's a record in the child table, it's current.

Put a foreign key constraint on the permanent child and parent (master)
tables and the users will never have to use the wizard-like features you're
building into the application now. They would just need to import the
current list (and possibly delete old records and compact the DB).
 
Thanks to both Granny Spitz and Douglas for your comments. I think I will
look at the alternatives, as the comments have got me thinking again. The
suggestion to create a table which will hold the current list, and linking it
to the master table should do the trick. All I then need to do is to create
buttons that will delete what is in the table and import the new list, when a
new current list becomes available.

Thanks
 
Back
Top