Android Question Could I get some Guidance here?

Discussion in 'Android Questions' started by Lary Yenta, Mar 6, 2015.

  1. Lary Yenta

    Lary Yenta Member Licensed User

    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
     
    Last edited: Mar 6, 2015
  2. RandomCoder

    RandomCoder Well-Known Member Licensed User

    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.
     
  3. Lary Yenta

    Lary Yenta Member Licensed User

    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
     
  4. KMatle

    KMatle Expert Licensed User

    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
     
    Mark Zraik likes this.
  5. walterf25

    walterf25 Well-Known Member Licensed User

    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
     
  6. Lary Yenta

    Lary Yenta Member Licensed User

    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
     
  7. walterf25

    walterf25 Well-Known Member Licensed User

    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
     
  8. Lary Yenta

    Lary Yenta Member Licensed User

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

    Thx,

    lary
     
    walterf25 likes this.
  9. RandomCoder

    RandomCoder Well-Known Member Licensed User

    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.
     
  10. Mark Zraik

    Mark Zraik Member Licensed User

    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
     
    RandomCoder likes this.
  11. Lary Yenta

    Lary Yenta Member Licensed User

    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
     
  12. klaus

    klaus Expert Licensed User

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

    Lary Yenta Member Licensed User

    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
     

    Attached Files:

  14. Lary Yenta

    Lary Yenta Member Licensed User

    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
     
  15. klaus

    klaus Expert Licensed User

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

    Mark Zraik Member Licensed User

    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
     
  17. Lary Yenta

    Lary Yenta Member Licensed User

    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
     
  18. Mark Zraik

    Mark Zraik Member Licensed User

    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
     
    RandomCoder and klaus like this.
  19. Lary Yenta

    Lary Yenta Member Licensed User

    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
     
  20. Lary Yenta

    Lary Yenta Member Licensed User

    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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice