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.

Reader Tables

1 Readers table

|Field Name |Type |Size |Description |

|ReaderID |integer |32 bits |Unique integer ID of reader |

|ReaderName |varchar(16,0) |16 chars |Textual name of reader |

|ReaderIPAddr |varchar(16,0) |16 chars |IP Address of reader |

|ReaderZoneID |integer |32 bits |Integer ID of reader zone |

|ReaderClassID |integer |32 bits |Integer ID of reader class |

|ReaderTypeID |integer |32 bits |Integer ID of reader type |

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".

2 ReaderZones table

|Field Name |Type |Size |Description |

|ReaderZoneID |integer |32 bits |Unique integer ID of reader zone |

|ReaderZoneName |varchar(16,0) |16 chars |Textual name of reader zone |

This table contains a textual description for each zone ID. A sample reader zone might be "First Door".

3 ReaderClasses table

|Field Name |Type |Size |Description |

|ReaderClassID |integer |32 bits |Unique integer ID of reader class |

|ReaderClassName |varchar(16,0) |16 chars |Textual name of reader class |

This table contains a textual description for each reader class ID. A sample reader class might be "Portal".

4 ReaderTypes table

|Field Name |Type |Size |Description |

|ReaderTypeID |integer |32 bits |Unique integer ID of reader type |

|ReaderTypeName |varchar(16,0) |16 chars |Textual name of reader type |

This table contains a textual description for each reader type. Sample reader types might be "Alien", "Symbol" or "ThingMagic".

5 ReaderExceptions table

|Field Name |Type |Size |Description |

|ReaderID |integer |32 bits |Reader identifier |

|ExcTime |Date |32 bits |Timestamp of entry |

|Description |varchar(64,0) |64 chars |Textual description of exception |

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.

Tag Tables

1 TagData table

|Field Name |Type |Size |Description |

|RFIDRaw |varchar(40,0) |40 chars (?) |Raw hex value of RFID, in string format. |

|TagFilterValue |integer |8 bits |Pallet? Pack? Individual item? Serves as index into |

| | | |TagFilters table. |

|EPCNumber |varchar(43,0) |43 char |EPC Number from tag |

|ReaderID |integer |32 bits |Reader ID |

|TagProtocolID |Integer |32 bits |Tag protocol type ID |

|ReadType |char |8 bits |'a' = arrival, 'd' = departure |

|ReadTime |Date |32 bits |Timestamp of entry |

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 TagFilters table

|Field Name |Type |Size |Description |

|TagFilterValue |integer |8 bits |1,2,3, etc |

|TagFilterDesc |varchar(64,0) |64 chars |For now: 1 = "Retail Consumer Trade Item", 2 = |

| | | |"Standard Trade Item Grouping", 3 = "Single |

| | | |Shipping/Consumer Trade Item" |

3 TagProtocols table

|Field Name |Type |Size |Description |

|TagProtocolID |integer |32 bits |1,2,3,4 etc |

|TagProtocolDesc |varchar(16,0) |16 chars |1 = "class 0", 2 = "class 1", 3 = "class 0+", 4 = |

| | | |"GEN2", etc... |

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.

1 TestData table

|Field Name |Type |Size |Description |

|TestID |integer |32 bits |1,2,3,4 etc |

|TestTypeID |integer |32 bits |Index into TestTypes table |

|CompanyID |integer |32 bits |Index into Companies table |

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.

2 TestTypes table

|Field Name |Type |Size |Description |

|TestTypeID |integer |32 bits |1,2,3,4 etc |

|TestTypeName |varchar(32,0) |32 chars |Description of test type |

This table allows the user to enumerate a number of test types. An example might be, "tag on top, aligned north-south".

3 RunData table

|Field Name |Type |Size |Description |

|TestID |integer |32 bits |Index into TestData table |

|RunID |integer |32 bits |Typically from 1 to 25 |

|StartTime |Date |32 bits |Timestamp of run start |

|EndTime |Date |32 bits |Timestamp of run stop |

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.

4 Companies table

|Field Name |Type |Size |Description |

|CompanyID |integer |32 bits |integer ID of company |

|CompanyName |varchar(32,0) |32 chars |Name of company |

Ideally, this might be the same list (with the same values) as the company list maintained by EPCGlobal. In reality, it may not be.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download