Guest kilbad Posted February 24, 2007 Share Posted February 24, 2007 I am building a php script that will collect a patient's demographic and contact information (name, address, gender, etc), as well as a form for a history to be typed up in (i.e. a paragraph describing the patient's problem, allergies, medications, etc.). I was thinking I would design a database for this information as follows: ~Each pt will be assigned a arbitrary seven digit chart number, and that will be used for a primary ID key in a table which will hold all of the patients demographic and contact information. ~Then, each patient will get there own table, named with his or her chart number. The primary ID key will be the date that the history was taken (because each time I see the patient again, a new history will be taken). However, my concern is that if I have several thousand patients in the DB, that will mean several thousand tables, is that ok? Is there a better db design I could use? Thank you all so much in advance! Brendan Quote Link to comment https://forums.phpfreaks.com/topic/39923-is-this-the-best-db-design/ Share on other sites More sharing options...
Yesideez Posted February 24, 2007 Share Posted February 24, 2007 Personally I'd go for two tables. One containing the personal details of the patient (let's call it "patients") and the other table (let's call it "history") How I'd structure "patients": patientid, mediumint( unsigned, primary index chartnumber, mediumint( unsigned name, varchar-40 address, text gender, enum('Male','Female') and any more static data about the patient etc. How I'd structure "history": historyid, mediumint( unsigned, primary index chartnumber, mediumint( unsigned problem, text allergies, text medication, text dt, int( unsigned and again, any more information about a patient's visit etc. As you add records to "patients" the patientid field will increase giving them a unique ID number. You can still enter their chart number in the next field which you can later use to search for patients and even link to the history table. The history table also has it's own unique "historyid" field but also has space to enter the patient's chart number. As long as you enter the patient's chart number correctly into the history every time then you can use that to link back to the patient table. You won't get one patient referencing another patient's details that way. If you really wanted to be extra safe about not making any mistakes, have it so that you enter the patient's chart number, submit that and it picks data from patients table to confirm you have the right person. If you made a typo entering the chart number you'll notice because the wrong name etc. will be shown. Next simply fill in all the information like allergies etc. and submitting the form can be set to auto fill the "dt" field using time(). Who's making this, only you? Where do you plan on keeping the data? On your computer or on a web server? If I was a patient I wouldn't be happy having my details being stored on a server. You might be better off learning Microsoft Access so you can keep and access the database on your own PC without even having it connected to the net. If I'm wrong with anything I've said I hope someone would point it out and correct me - thanks. Hope this is of help and gives you something to think about Quote Link to comment https://forums.phpfreaks.com/topic/39923-is-this-the-best-db-design/#findComment-193241 Share on other sites More sharing options...
Guest kilbad Posted February 24, 2007 Share Posted February 24, 2007 @Yesideez - Thank you so much for your help. That sounds like a better idea! To follow-up on your questions, I am the only one making this. I am a medical student, and I am coding a simple medical record system to be run on my dedicated server (LAMP) which I plan to use when I setup my clinic in Peru (a life goal of mine). I am sure there are open source systems already available, but coding is a hobby of mine, and I want to do it myself so it is just how I want it, and so I can track all the epidemiological data I want. Also, I realize patients probably would not want their data being stored on a server, but it will be secured, and confidential between just us. Thank you again for all your help! If you have any other feedback, please let me know! Sincerely, Brendan kilbad.com Quote Link to comment https://forums.phpfreaks.com/topic/39923-is-this-the-best-db-design/#findComment-193254 Share on other sites More sharing options...
Yesideez Posted February 24, 2007 Share Posted February 24, 2007 I've found having your own goal (personal reason) for getting something done is probably the best form of motivation because it's for you and not for someone else. I don't think I've ever used scripts written by other people. The reason why is because I then know exactly what the script can and cannot do and there won't be any surprises in store at a later date. I'm also able to make sure that any location in the scripts where input is processed from the user I can make sure security is in place to protect from things like MySQL injection or any other method a (prospective) intruder may want to use to gain unauthorised access. Anothing positive thing about doing it yourself is that not only will you learn new things about coding (along the way) but as you have already said, you'll have total control. You'll have everything presented as you want it and you'll be able to have it process data exactly as you want it. btw, the ( is actually the number 8 in brackets. All the best in your project, hope everything goes well for you Quote Link to comment https://forums.phpfreaks.com/topic/39923-is-this-the-best-db-design/#findComment-193260 Share on other sites More sharing options...
Yesideez Posted February 24, 2007 Share Posted February 24, 2007 btw, another reason for having a historyid as the primary key could be to use to build like a reference number for accessing the history table quickly when you need to access a specific piece of data. For example, displaying something like "chart number/historyid" in a corner looking something like "8154339/34" (8154339 would be my chart number and 34 would be the historyid. If I've seen you twice in one day then the historyid mixed with my chart reference number would point to that particular visit. Just an idea I thought I'd share (I used to work at both city hospitals) Quote Link to comment https://forums.phpfreaks.com/topic/39923-is-this-the-best-db-design/#findComment-193268 Share on other sites More sharing options...
Guest kilbad Posted February 25, 2007 Share Posted February 25, 2007 Concerning the first table, is it redundant to have a patient id and chart number? Just curious about your thoughts. Also, thanks for your responses.. I enjoyed reading them. Brendan Quote Link to comment https://forums.phpfreaks.com/topic/39923-is-this-the-best-db-design/#findComment-193353 Share on other sites More sharing options...
Yesideez Posted February 25, 2007 Share Posted February 25, 2007 I suppose the patientid number would be redundant so I suppose you wouldn't have to have both, just the chart number - I can't think of anything that patientid could be used for You mention that you have several thousand patients. For that amount of data I'd try and break down the history table as much as possible to prevent unnecessary duplication of data. For example, I'm in the middle of creating a music/clubbing website and I've moved a list of music genres and locations into separate tables. If I have the need to add new genres or locations I just add them to their table and nothing more needs to be done. Quote Link to comment https://forums.phpfreaks.com/topic/39923-is-this-the-best-db-design/#findComment-193601 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.