Relationships question

[Hammurabi]

I'm a student and I am using Xataface for our big project and so far I am super impressed with what I have been able to get working! Thanks so much for a great program! I had a quick question about relationships though. Say I have 4 tables...

Patient(pat_id, Firstname, LastName,...)
Doctor(doc_id, Firstname,LastName,...)
FirstName(fn_id, fname)
LastName(ln_id, lname)

The Firstname and Lastname fields in Patient are foreign keys in my database to fn_id and ln_id. So, when I pull up the patient tab in xataface the Firstname and Lastname fields would have say, 1,1 in them since that would be the the first entries in each firstname and lastname tables. How would I relate them in Xataface so that when I pull up the patient table it would show the names associated with the id's instead of just the number? Is this possible? I appreciate the help in advance!

[shannah]

First comment is that your database is a little "too" normalized. I'm not sure why you would want to store firstname and last name in its own table.

There are a few ways to do this. Two of them are:

1. Grafted fields
==============
But as an academic exercise, you can achieve what you want by grafting the actual first name and last name columns onto the patient table by defining an __sql__ directive in your patients fields.ini file.

e.g.

Code: Select all

__sql__ = "select p.*, f.fname as firstname_string, l.lname as lastname_string
   from Patient p
   left join FirstName f on p.Firstname=f.fn_id
   left join LastName l on p.LastName=l.ln_id
"


This will effectively graft on the calculated fields firstname_string and lastname_string onto your Patients table. They would be read only, but you could configure and use them like any other column.

2. Using vocabularies
==================

Set up firstname and lastame vocabularies in your valuelists.ini file. Then use the "select" widget for the firstname and last name.
e.g. fields.ini

Code: Select all

[Firstname]
   widget:type=select
   vocabulary=firstname

[LastName]
    widget:type=select
    vocabulary=lastname


Your valueslists.ini might look something like

Code: Select all

[firstname]
    __sql__ = "select fn_id, fname from FirstName order by fname"

[lastname]
    __sql__ = "select ln_id, lname from LastName order by lname"


All that said, I still wouldn't go with this type of db design unless you have a very good reason. Keeping a separate table for first names and last names doesn't seem beneficial in any way.

[Hammurabi]

Thanks for the information! I have been working on it all day, and I was thinking that it was not worth the trouble as well. Our teacher wanted it in 3NF is the only reason we normalized it so much. I think that I will just have to make the executive decision to get rid of the first and last name tables. Thanks again!

[shannah]

You could have the first and last names in the Patients table and it would still be 3NF. (Since the first name of a patient is dependent on the primary key of patient and only the primary key of patient.... in other words you could change any other field in patient and it would not have any effect on the first name or last name).