How do I design a configurable checklist table for my database?

I am working on a database model and one of the areas I need开发者_Python百科 to address is a client configurable checklist table(s). Ideally the client would have a set of predefined checklists that I could then enter as bit columns. Alas, this is not going to happen. The client wants the ability to add and group checklists so they are reusable.

I could go with an EAV type model and use strings for "true/false" but I've been down this road before and would rather not take that journey again.

Any thoughts or suggestions are welcome. Thanks.

"Client Configurable" almost always means EAV. EAV's store data fine... it's just getting the data back out that's the trick.

;-)

The other... somewhat-less objectionable approach is to make generic columns. Answer1 BIT, Answer2 BIT, Answer3...

If you do this, then make a table where you define the real names of the columns so that you could build the correct view for any given check list.

Say the first check list is

• Gas in the tank?
• Money in the Wallet?

Then store

 CheckList_ID     Answer_Number     Column_Name
     1                 1              Tank has Gas
     1                 2              Wallet has Money.

Then the view would be:

 SELECT Answer1 "Tank has Gas", Answer2 "Wallet has Money"
  FROM Generic_Answer_Table
  WHERE CheckList_ID = 1

It obvious how the view would be dynamically generated.

The downside is that if you include 10 columns they can't have more than 10 EVER.

XML is the other possibility and doesn't suffer that issue... however it's not quite as SQL friendly.

I'm thinking XML will be the best way to give them the control they seek along with keeping the check boxes dynamic. Maybe an XML file per page/section that contains all the custom check boxes. Whats good about XML is that its easy to work with and gives you great flexibility. At the database level just save the output of the check boxes as finalized data, just in case the check box source XML changes you will still see the originally selected values saved at the database level.

We're building http://tallyfy.com for such a purpose, although a direct relation between actions and your database table is something new.