"RFID Database Schema Proposal"
RFID Database Schema Proposal September 19, 2005 This document serves as the first cut of the database schema for an RFID reader system. For now, we are focusing on readers, tag information and test-related data. Later, additional information (such as conveyor belt information) may be added. 1 Reader Tables 1.1 Readers table Type integer varchar(16,0) varchar(16,0) integer integer integer Size 32 bits 16 chars 16 chars 32 bits 32 bits 32 bits Description Unique integer ID of reader Textual name of reader IP Address of reader Integer ID of reader zone Integer ID of reader class Integer ID of reader type Field Name ReaderID ReaderName ReaderIPAddr ReaderZoneID ReaderClassID ReaderTypeID This is the main table for RFID Reader information. It is heavily influenced by the AR-400 API, which allows for a Zone and a Class to be associated with each reader. Examples of reader names would be "portal1-rdr1" or "conv2-rdr5a". 1.2 ReaderZones table Type integer varchar(16,0) Size 32 bits 16 chars Description Unique integer ID of reader zone Textual name of reader zone Field Name ReaderZoneID ReaderZoneName This table contains a textual description for each zone ID. A sample reader zone might be "First Door". 1.3 ReaderClasses table Type integer varchar(16,0) Size 32 bits 16 chars Description Unique integer ID of reader class Textual name of reader class Field Name ReaderClassID ReaderClassName This table contains a textual description for each reader class ID. A sample reader class might be "Portal". 1.4 ReaderTypes table Type integer varchar(16,0) Size 32 bits 16 chars Description Unique integer ID of reader type Textual name of reader type Field Name ReaderTypeID ReaderTypeName This table contains a textual description for each reader type. Sample reader types might be "Alien", "Symbol" or "ThingMagic". 1.5 ReaderExceptions table Type integer Date varchar(64,0) Size 32 bits 32 bits 64 chars Description Reader identifier Timestamp of entry Textual description of exception Field Name ReaderID ExcTime Description This is sort of a "catch-all" table for all errors, exceptions, and events (excluding tag read events) that we want to store for each reader. It can contain fairly benign entries (like the reader being enabled or disabled) or fairly serious errors (like the reader detecting a malfunctioning antenna). We may well want to break this up into sub-tables. 2 Tag Tables 2.1 TagData table Type varchar(40,0) integer varchar(43,0) integer Integer char Date Size 40 chars (?) 8 bits 43 char 32 bits 32 bits 8 bits 32 bits Description Raw hex value of RFID, in string format. Pallet? Pack? Individual item? Serves as index into TagFilters table. EPC Number from tag Reader ID Tag protocol type ID 'a' = arrival, 'd' = departure Timestamp of entry Field Name RFIDRaw TagFilterValue EPCNumber ReaderID TagProtocolID ReadType ReadTime The TagData table contains tag-read entries for the RFID system. Each entry in the table contains a timestamp, information about an RFID tag, and a ReaderID. The RFID tag information is stored both in it's raw form (RFIDRaw, looks like " 98022475ea000001") and in a somewhat interpreted form (EPCNumber, looks like "sgtin:0078742.146165.154432"). While this may be redundant, it makes post-process query-writing much more convenient. Since EPCNumber does not include filter or tag protocol information, TagFilterValue and TagProtocolID are also included as fields. Note that this table format supports the two operational modes in which we are interested: "Factory" mode: Log one entry per tag per reader. "Test" mode: Log an entry each time a reader detects a tag arriving or departing. This allows us to gauge the effectiveness of a reader/tag combination. 2.2 TagFilters table Type integer varchar(64,0) Size 8 bits 64 chars Description 1,2,3, etc For now: 1 = "Retail Consumer Trade Item", 2 = "Standard Trade Item Grouping", 3 = "Single Shipping/Consumer Trade Item" Field Name TagFilterValue TagFilterDesc 2.3 TagProtocols table Type integer varchar(16,0) Size 32 bits 16 chars Description 1,2,3,4 etc 1 = "class 0", 2 = "class 1", 3 = "class 0+", 4 = "GEN2", etc... Field Name TagProtocolID TagProtocolDesc 3 Test Tables These tables assist us in tracking test data for our RFID application. Typically, a test is comprised of 25 or so "runs". A run involves moving a pallet (or set of pallets) into a reader zone, then out of a reader zone. When analyzing each run, arrival and departure times are checked for each tag that was involved in the run, and the performance of the readers is measured based upon that information. 3.1 TestData table Type integer integer integer Size 32 bits 32 bits 32 bits Description 1,2,3,4 etc Index into TestTypes table Index into Companies table Field Name TestID TestTypeID CompanyID This is the top-level test table. It contains an ID for each test, along with the test type and the company for which the test was performed. 3.2 TestTypes table Type integer varchar(32,0) Size 32 bits 32 chars Description 1,2,3,4 etc Description of test type Field Name TestTypeID TestTypeName This table allows the user to enumerate a number of test types. An example might be, "tag on top, aligned north-south". 3.3 RunData table Type integer integer Date Date Size 32 bits 32 bits 32 bits 32 bits Description Index into TestData table Typically from 1 to 25 Timestamp of run start Timestamp of run stop Field Name TestID RunID StartTime EndTime This table captures the start and stop time of each individual run for each test. The actual tag information is captured in the TagData table. 3.4 Companies table Type integer varchar(32,0) Size 32 bits 32 chars Description integer ID of company Name of company Field Name CompanyID CompanyName Ideally, this might be the same list (with the same values) as the company list maintained by EPCGlobal. In reality, it may not be.