Android Question Could I get some Guidance here?

[Lary Yenta]

Hey all, I am hoping someone with a lot of Database experience can give me a bit of a hand here. In my database for my app, I have several tables but I am going to ask my question concerning only 2 of them and just extrapolate from there.

My first table is all of a user's identifying information, ID as primary Key and then stuff like Name, Address, etc etc.. The second table is my medication table which is to contain, for each user, a list of their medications. The structure of the meds table is UserID (Integer which joins the first table and this one), MedID (Right now, the Primary Key), MedName, etc etc.. Would I be best making, for the Meds Table, UserID AND MedID as key? If I did that, could my tables possibly look like the following?

User Table.....................Meds Table
ID=1.............................UserID=1
Name=Lary....................MedID=1
...................................MedName=Whatever
...................................UserID=1
...................................MedID=2
...................................MedName=Stuff​

ID=2.............................UserID=2
Name=George.................MedID=??? 1 OR 3???
...................................MedName=DifferentStuff​

I hope that this is clear. Any suggestions?

Thanks,

Lary

 

[RandomCoder]

Firstly, I wouldn't class myself as an expert at anything so please take my following advice as just something to consider.
I think that your right with the user table, but with regard to the meds table I would just store details specific to that medication i.e. name of medication, precautions (eg. to be taken 1 hour before food), side effects, emergency advice etc. Anything specific to the medicine and common to all users which might be useful in your app.
I'd then have a third table maybe called prescriptions (it's the first name that sprang to mind, I'm sure you can come up with better). This would contain the useId, and the medId thereby providing all the details required for either the user or the medicine just by selecting the corresponding entry from either table. Also in the prescriptions table would be the time the medicine needed to be taken, the quantity, and any specific advice for the user.

I think that there will be less duplication doing it this way which is what relational database's are all about.

 

[Lary Yenta]

Hi RandomCoder,

Well, the Meds table is really the same as the prescription table as you specified it. The doctors that I have spoken to did not really think that a lookup table was a good idea. To me, their reasoning made sense because of all of the different variations of the different medications. In the meds table I have the following fields: UserID, MedID, MedName, Dosage size, Frequency and Comments.

I was really asking the question more about the indices.

Thanks for your thoughts though!!!

Lary

 

[KMatle]

You could a third (or even more) "relation" table which contains "what the user is assigned to". This could be anything which is the key to many solutions. F.e. the doctor makes an appointment with someone. In this table you have one row (key is the unique User-ID) with a type "appointment" followed by the time schedule.

Another row in the same table has the type "prescription" with an own id which you use to get to the prescription table.

Benefit is: With one Select you can show all header data and filter it (f.e. show all presriptions between x and y). In a second step you can get the details from the table (f.e. get details about the prescription).

Example:

User-table

User-ID: 23
Name: Smith


Relation table:

Row1
User-id: 23
Type: 2 (for appointment)
Rel.ID: 125

Row2
User-id: 23
Type: 5 (for prescription)
Rel.ID: 256

Appointment-table:

Rel.ID: 125
Start: 08:00
End: 08:29
What: Talk about prescription


Prescription-table:

Rel.ID: 256
Presp_Nr.: 1
Presp: Aspirin
Dosis: 3x1

Rel.ID: 256
Presp_Nr.: 2
Presp: Beer
Dosis: 12x1

 

[walterf25]

I agree with Kmatle, building a relational database is the best to go, I could go into a lot more details about an easier way to about this but i will let you do some research so you can learn more about sqlite. You can start here http://www.tutorialspoint.com/sqlite/sqlite_using_joins.htm.

Cheers,
Walter

 

[Lary Yenta]

Hey there Walter! I agree as well but for the time being I am really just trying to learn B4A. I want to go to a much more detailed relational database down the road but for now I am trying to keep it as simple as I can. Right now, I have one page showing my identifier which is basically Name, Address, Phone that kind of stuff. The next page shows the user's meds. I join the two tables by using the users ID field to the meds UserID field. My problem comes when I run the App. The first time I run it, No Problem, I create all of my tables. So, now I can add in the initial user, BUT when I want to add in meds, I keep referencing an empty table because I haven't added any meds yet!

I figure that I am doing something wrong inside my code but for the life of me, I can't seem to get it figured out!!! GRRRR

Any suggestions?

Lary

 

[walterf25]

Hey there Walter! I agree as well but for the time being I am really just trying to learn B4A. I want to go to a much more detailed relational database down the road but for now I am trying to keep it as simple as I can. Right now, I have one page showing my identifier which is basically Name, Address, Phone that kind of stuff. The next page shows the user's meds. I join the two tables by using the users ID field to the meds UserID field. My problem comes when I run the App. The first time I run it, No Problem, I create all of my tables. So, now I can add in the initial user, BUT when I want to add in meds, I keep referencing an empty table because I haven't added any meds yet!

I figure that I am doing something wrong inside my code but for the life of me, I can't seem to get it figured out!!! GRRRR

Any suggestions?

Lary

sure, i'm not sure i understand exactly what the problem is, would you give more details, and maybe share the relevant piece of code?

Thanks,
Walter

 

[Lary Yenta]

sure, i'm not sure i understand exactly what the problem is, would you give more details, and maybe share the relevant piece of code?

Thanks,
Walter

Tell you what, I will send you what I have right after I get back from grocery shopping!

Thx,

lary

 

[RandomCoder]

Hey there Walter! I agree as well but for the time being I am really just trying to learn B4A. I want to go to a much more detailed relational database down the road but for now I am trying to keep it as simple as I can. Right now, I have one page showing my identifier which is basically Name, Address, Phone that kind of stuff. The next page shows the user's meds. I join the two tables by using the users ID field to the meds UserID field. My problem comes when I run the App. The first time I run it, No Problem, I create all of my tables. So, now I can add in the initial user, BUT when I want to add in meds, I keep referencing an empty table because I haven't added any meds yet!

I figure that I am doing something wrong inside my code but for the life of me, I can't seem to get it figured out!!! GRRRR

Any suggestions?

Lary

Hi Lary, I'm not sure how making your database more relational affects your app? Sure it will alter the queries slightly but the flow of your program should remain the same.

 

[Mark Zraik]

Hi Lary,

Hope you got some good grub at the store.
It has been a long time since I messed with RDB's, but Kmatle eluding to the relations table is what is easiest to pull all the data together.
Since your Meds are going to be different for each patient(User), you'll need the relational table to store those unique values.
The meds table will have all the details of the Medication, but not the dosage, etc.
The user table will have all the info about the user(Patient), but nothing else.
There might be other tables as well.

The relational table that pulls this together becomes a little more simplified by only needing the ID's listed from other tables.
relational table:
userID,medId,details of this medication for this prescription...
userID,medId,details of this medication for this prescription...
userID,medId,details of this medication for this prescription...
userID,medId,details of this medication for this prescription...

It will be fun!
Mark

 

[Lary Yenta]

Hi there folks,

I have decided to go with the relational tables but I am going to have to get a source for that table. I spoke to my pharmacist, also a diabetic, who is going to supply me with a table that contains medication names, Dosage sizes, common side effects, common instructions, in short, the whole ball of wax for the medication. I am still running into the same issue though. Here is the "lay of the land" so to speak.

I used Klaus's SQLiteLight2 program as a starting point. My first step was I changed Klaus's Table and created a new persons table. I got that part going without a hitch. Then, recognizing that I was going to need some other tables, I also created those at the first time run. These other tables were: Meds(which will be tied over to the drugs table, once I get it, of course), Contacts (I created this table in case a diabetic was found unresponsive so that there would be a family member to notify, doctors who know the person, etc), Measurements table (for the diabetic to record their data from measurements that they have taken).

Of course, since I base the opening of the persons(users table) on Klaus's code, I can get it to work with no issues. So, now, I want to open the meds table, I can't get it to open that table at all. The same with the Contacts table or the measurements table. Like I said, it is probably just something stupid on my part, but for the life of me, I just can't seem to get it!

I hope that this explains things better?

Thanks,

Lary

 

[klaus]

How do you access the other tables, you should show us your code.

 

[Lary Yenta]

Klaus,

That is the problem Klaus, I am not able to access the other tables. I will include the project code so far here for you.

Like I said, probably something stupid I am doiing here!

Lary

 

[Lary Yenta]

DOH!!!!!

Manohman, Remember I said that I had to be making some kind of stupid mistake? Well, I know what it was and I was absolutely right; my mistake WAS completely idiotic! I had not initialized my SQL2 Variable.....COMPLETELY idiotic on my part!!!

Thanks Guys...NOW you have to promise NOT to call me an idiot!

Lary

 

[klaus]

You don't need SQL2 you could do everything with SQL1 as it's the same database.

 

[Mark Zraik]

Hi Lary,

Your using a more recent version of B4A then I; Using 3.82.
So I did not compile your example, but I did look through the code.

It appears that you are using the persons table as the "main" table, and are going to relate the other table entries to "persons", which is fine.
Add some Text to "persons" to include medsIDs Text, apptID Text, doctorsID Text, emContactID Text. You will use them down the road.

Having to deal with TypeII myself, I think I know the road you're on, but it's only a guess.
My suggestions are only that, suggestions. You can throw them in the trash if you want, but read the following.

RDB's what's the point?

Using an RDB allows you to compartmentalize each Nugget of the whole picture. You can enter, edit, delete and update parts of the Nugget that don't effect the other parts of the picture until you pull it all together for display (Access) through a form(Desktop), Activity(Android) using an SQL Statement.
Having each Nugget separate allows the re-use of a Nugget Item for many other relationships. "One to Many" One "person" with Many Meds, Contacts, Doctors, etc.

Somewhere in your future is the SQL - Joins, Where, etc., Statements that you will use to pull this together. Just because you have a Text Type entry as a Row or Column, doesn't mean it can't contain a space separated, comma separated list of values.

I might be ahead of the project here, but I sense that you will end up on this path.

Hope I am helping,
Mark

 

[Lary Yenta]

Hi Mark,

Your comments are appreciated very very much. I got over quite a hump yesterday and all it took was some slugging it through. Something Klaus had noted helped me a lot but now I am a bit hung up with something. To open my second page or the meds table, I will create the query "Select T2.UserID, T2.MedID from meds T2 Left Outer Join persons T1 on T1.ID=T2.UserID" but I am having a few issues with automatically assigning the MedID...I am going to continue slugging it out though.

Cheers,

lary

 

[Mark Zraik]

Hi Lary,

Sometimes I have even borrowed my neighbors hammer, there's nothing like a vertical learning curve!
Keep at it!

I'm not sure exactly what you mean when you say assigning the MedId. Are you trying to assign the med to the person? Or the person to the Med?
I also don't know what amount of data you have "The whole ball of wax".

I'm guessing that you want to assign the med to the person, so, All the meds available to add to the person are in a table "meds".

Just an idea---
On this second page, use a select * from meds statement to fill a list, there should be other textviews and possibly listviews as needed. Short Click on the list to add a medication, Long click to read the details.
The Short Click selects a med and pre-fills the appropriate textviews and maybe even some other listviews from all the data contained in the medication table as it pertains to the medID selected. You could then choose the right dosage, etc., and then use a button to run an insert/append sql statement that adds the med to the Table "meds_issued" and includes the personID. Use another activity to show the person and all the meds assigned/issued to the person with dosages, etc.

I realize I may be shooting in the dark, but as I said, I've wrangled with the beast TypeII and for now have it beaten into submission, so I really can relate to the overwhelming details that go along with it. Appointments, Meds(Multiple), Lab Test, Other ailments that shoot off of Diabetes and so on.

A list of tables banging around in my head are:
patient - What I would use for the "main" table to pull it all together
person - for the name and address, email, etc., of the person/s added to the patient table - You could have more than 1.
meds - for all the meds
lab_test A1C's and so on
sugar_level (Glucose) readings
appointments - eye, general, specialist, etc.
doctors - names, groups
insurance company
emergency contacts
what I want for my birthday - OKAY, that one doesn't count!

There would be an interface to append, update, and delete for each table.
Once that part was running smooth, then I would worry about pulling it all together.

I hope these ramblings can help in some way.

For a good picture of Joins, See the links below.
The following is Annotated
http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins
Source: Visual-Representation-of-SQL-Joins explained in detail by C.L. Moffatt
And another http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
End of Annotation

Mark

 

[Lary Yenta]

Hi Mark,

Sorry for the late reply but I have been buried in code for the last couple of days. This afternoon I am going to see one of the professors at the University of Ottawa Med School who contacted me about a complete list of prescriptions for diabetics patients. I will let you all know what he has to say.

Lary

 

[Lary Yenta]

Hi Mark,

Sorry for the late reply but I have been buried in code for the last couple of days. This afternoon I am going to see one of the professors at the University of Ottawa Med School who contacted me about a complete list of prescriptions for diabetics patients. I will let you all know what he has to say.

Lady