mysql - Database schema: Key/Value table or all keys in one record -


I think this is part of the philosophical question. I need to collect pathology results for the group of patients and store them in the database. In the past, I have used a very simple table structure (simplified): 

  + --- ---------------- + ---- ---------- + ------ + ----- + ----- ---- + ------- + | Field | Type | Faucet Key | Default | Extra | + ------------------- + -------------- + ------ + ----- + - -------- + ------- + | ID | Bilent (20) | No | PRI | Faucet | | Updated | Day-time No | PRI | Faucet | | Patient ID Varchar (255) | No | | Faucet | | Name | Varchar (255) | No | | Faucet | | Price | Varchar (255) | No | | Faucet | + ------------------- + -------------- + ------ + ----- + - -------- + ------- +

I look at the schema design more often: 

  + - - ---------------- + -------------- + ------ + ----- + ---- ------ + ------- + | Field | Type | Faucet Key | Default | Extra | + ------------------- + -------------- + ------ + ----- + - -------- + ------- + | ID | Bilent (20) | No | PRI | Faucet | | Patient ID Varchar (255) | No | | Faucet | | Ph_Value | Varchar (255) | No | | Faucet | | K_Value | Varchar (255) | No | | Faucet | | Ca_Value | Varchar (255) | No | | Faucet | | Ph_Value_updated | Day-time No | | Faucet | | K_Value_updated | Day-time No | | Faucet | | Ca_Value_updated | Day-time No | | Faucet | + ------------------- + -------------- + ------ + ----- + - -------- + ------- +

It seems that the first design is more flexible, expandable. However, I wonder if the records run for millions when the performance hits.

The issue with the other is that there can be few hundred farms which should be recorded on occasions.

I would really be interested in getting comments / advice / guidance.

You are absolutely right, the first schema is very flexible: you can add new keys to change the schema Without a live database, however, flexibility is usually purchased with time and / or space. In this situation, it is both: You need more space to store all keys for the same line because < Code> id has been repeated N bar, and appears The required order fields will take time to receive.

There is no reason to pay for flexibility unless you require it. If most of your questions require most columns, then the second result is the most economical, however, if most of your questions ask for a column, then getting the flexibility can be worth spending on CPU time and database space.


Comments

Post a Comment

Popular posts from this blog

ios - How do I use CFArrayRef in Swift? -

I am using Objective-class in my Swift Project through a bridging header. The method signature looks like this: - Some cement (some type) some parameters; I started by getting an example of class, calling method, and storing the value: var myInstance = MyClassWithThatMethod (); Var cfArr = myInstance.someMethod (some value); Then try to get the value in the array: var valueInArrayThatIWant = CFArrayGetValueAtIndex (cfArr, 0); However, I get the error unmanaged & lt; Cfarray & gt; Not like 'CFArray' . Unmanaged & lt; Cfarray & gt; also means? I looked, but I do not need to change the array in a fast array (though it would be good). I need to be able to get value from the array. I also tried CFArray method to pass in a function: func doSomeStuffOnArray (myArray: NSArray) {} Although I get the same error when using: doSomeStuffOnArray (cfArr); // unmanaged & lt; CFArray & gt; I am using CFArray because I want to sto...
Read more

eclipse plugin - Run java code error: Workspace is closed -

To create an automated project, I created a plug-in project with the following dependencies: Org.eclipse.core.resources org.eclipse.equinox.registry org.eclipse.core.runtime and the following The Java class is located in the src folder: Package Examiner; Import org.eclipse.core.resources.IProject; Import org.eclipse.core.resources.IWorkspaceRoot; Import org.eclipse.core.resources.ResourcesPlugin; Import org.eclipse.core.runtime.CoreException; Import org.eclipse.core.runtime.IProgressMonitor; Import org.eclipse.core.runtime.NullProgressMonitor; Public class tes {public static zero main (string [] args) {// TODO auto generated method stub IProgressMonitor progress monitor = new NullProgressMonitor (); IWorkspaceRoot root = ResourcesPlugin.getWorkspace (). GetRoot (); Ipoject project = root.jetproject ("desired projectname"); Try {Project.create (progress monitor); Project.open (progressMonitor); } Grip (CoreException E) {// TODO Auto generated blocking block e....
Read more

c - Error on building source code in VC 6 -

When I create my project in VC6, the BScMKE reports the following error Linking .. Creating Library ... .. / ... / PB / Bean / Debub / ICEERP.Lib and Object ........ P.B. / Bean / DBube / ICREPlay.APP Browse information file ... BSMMK: ​​error can not be read from BK 1505: file '../../PB/bin/TmpOut/ICReplay/Debug/ICReplay_PB.bsc' Error executing bscmake.exe Can someone help me? 2 Reason for reference from: "ICReplay_PB .bsc "file will be corrupted The trunk of the file will be happening with the compiler running out of disk space or is being interrupted when creating .sbr file I think this answer will help you!
Read more