Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sapator
Constraint Violating Yak Guru
462 Posts |
 Posted - 2011-11-28 : 20:36:34
|
| Hi.I am creating a doctors general db and what i have now is the need to create the general examinations.I am thinking of 2 options.1)Do it in many tables.Since there are examinations for the heart,for allergies,for blood etc.2)Do it all in one table.Normally you would suggest no1 but the trick here is that all the examinations will be in one pass, meaning a page that every examination of every condition value must appear with a select.Also there are no relationships between them and they only have a PK of patient_exam_id.So the question is if i would put the examinations in one row(so 40-50 rows on patience_exam table) or in 3-4 tables(with each one with a patient_exam_id).If i put them on Multi-tables then i guess i gain for an update of a specific examination type but if i have to update all examinations i would be calling all the examination tables.If i put them on a single table then i gain a single row update but i am updating a row with 40-50 columns.Again let me note that there is no other relationship in the examination but the patient_exam_id.What do you think?thanks. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-29 : 05:43:57
|
| >> Normally you would suggest no1 I wouldn't.It's the difference between an examination for a heart or allergy or blood and a heart examination or allergy examination or blood examination.From what you say (patient_exam_id) it sounds like you think of it as an examination with attributes.Even worse for 1 it would mean creating new tables whenever a new examination type comes along.If you need to update 40-50 columns for an examination then there's no way round that - you have the option of one table with 40-50 columns or and indetermination number of tables with 40-50 columns.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-11-29 : 18:03:45
|
| Hi.Yes that's why i've said there are no special conditions involved.As you say it's an examination with attributes so i was thinking of no2 but i wanted some opinions.I was thinking of 40-50 columns update vs multi table update but what i was not sure is if that amount of columns will slow the performance down. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-11-29 : 18:12:46
|
| If you are seriously considering (2) then you don't need (or want) a relational database. The only thing you want to do is look up a document that has the details on it? you don't need to compare columns to each other or run different relational type queries?You should consider a document store instead. Maybe something like SOLR? It's pretty awesome. And free.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2011-11-29 : 18:46:45
|
| No the whole database is relational all right.It's that little part i had hesitation about.Thanks. |
|
|
|
|
|
|
Opinion about single row vs multi tables