Re: [sqlite] Single large table vs. several smaller tables

RSmith Thu, 09 Oct 2014 07:45:53 -0700

There is no point to grouping similar fields in different tables, though I would advise grouping them together in the main table asa simple case for clarity, but it has no other benefit. As long as the data is in 1NF, one table is fine. The main reason (and Ithink only motivation) to have data in seperate tables is if some column or set of columns repeats the very same information overand over, or if you have multiple-linking of sub data items to a main index - none of which seems to be the case in your table, allthe data is needed and possibly quite different for every test/entry.

Another reason people might use different tables is when you have large text or blobs that might make querying a bit faster if themain table has a simple smaller fieldset and then references another table 1-to-1 when retrieving the very large data - again,seemingly not the case for you.

Any other breaking up of a table is unneeded complication - if the data belongs together, keep it together - though if someone elsehas a thought on why it is reasonable to split up such data, I'd be interested to hear it too.


Have a great day!
Ryan


On 2014/10/09 16:25, Drago, William @ MWG - NARDAEAST wrote:

All,

An automatic test system that I designed generates 25 data elements for each 
unit tested. We test about 50 units/day. This data is currently being stored in 
a csv file. I'd like to move this over to an SQLite database.

The question I have is, should I lump everything together in one table just 
like the .csv file or should I create several smaller tables that group similar 
parameters? I'm not sure what would normally be done. I think the database is 
normalized properly in either case.

Existing records will never be modified once they are inserted. We will 
occasionally read the database to gather statistical information.

I'd like to get your input before I commit to anything that I might regret 
later.

Thanks,
-Bill


********** Single table **********

CREATE TABLE UUT(
                 DatasetID INTEGER PRIMARY KEY,
                 Location TEXT,
                 ModelNumber TEXT,
                 SerialNumber TEXT,
                 TestDate TEXT,
                 ATE TEXT,
                 CalStatus TEXT,
                 Par0 TEXT,
                 LFvOffset REAL,
                 LFv10 REAL,
                 LFv20 REAL,
                 LFv50 REAL,
                 LFv100 REAL,
                 LFv200 REAL,
                 HFvOffset REAL,
                 HFv10 REAL,
                 HFv20 REAL,
                 HFv50 REAL,
                 HFv100 REAL,
                 HFv200 REAL,
                 LFTarget REAL,
                 LFSource REAL,
                 LFPind REAL,
                 HFTarget REAL,
                 HFSource REAL,
                 HFPind REAL
);



********** Multiple Tables **********

CREATE TABLE UUT(
                 DatasetID INTEGER PRIMARY KEY,
                 Location TEXT,
                 ModelNumber TEXT,
                 SerialNumber TEXT,
                 TestDate TEXT,
                 ATE TEXT,
                 CalStatus,
                 Par0,
);

CREATE TABLE LFCal(
                 DatasetID INTEGER REFERENCES UUT,
                 vOffset REAL,
                 v10 REAL,
                 v20 REAL,
                 v50 REAL,
                 v100 REAL,
                 v200 REAL
);

CREATE TABLE HFCal(
                 DatasetID INTEGER REFERENCES UUT,
                 vOffset REAL,
                 v10 REAL,
                 v20 REAL,
                 v50 REAL,
                 v100 REAL,
                 v200 REAL
);

CREATE TABLE LFPowerLevels(
                 DatasetID INTEGER REFERENCES UUT,
                 Target REAL,
                 Source REAL,
                 Pind
);

CREATE TABLE HFPowerLevels(
                 DatasetID INTEGER REFERENCES UUT,
                 Target REAL,
                 Source REAL,
                 Pind
);

--
Bill Drago
Senior Engineer
L3 Communications / Narda Microwave East<http://www.nardamicrowave.com/>
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com<mailto:william.dr...@l-3com.com>


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Single large table vs. several smaller tables

Reply via email to