recommended db-design for sparsely populated 2D-table?

[Glenn]

Hello,
I'm looking for advice on a good design for what amounts to a 2
dimensional sparsely populated table (or matrix), considering speed,
storage and ease of use with queries.
the intended use is to simplify and centralize the data of multiple
access control systems.

the first column is a unique id.
the other columns are to hold as value 0(=off) or 1(=on).
the number of columns will typically be about 20; the number of rows
about 5000.

the corresponding table will mostly hold zeroes, about 3/4ths.
There will be several of such tables with varying numbers of columns.

queries would be: "list all rows with actived (on) 4th and 5th fields"

-Using a normal table design (20 odd fields per table) this amounts to
alot of 'wasted' space.
queries however are straight-forward.

-using a 2 column table-design
first column = ID
second column = a string sequence "on-off". "00100010000000000010"

this is more storage efficient. but queries and speed will suffer.

Instead of the string, the corresponding numeric value could be used.
the query would then need bit-level operators. not the most
readable/easy to use

suggestion, advice is most welcome.
best regards

 

[Jeff Boyce]

Glenn

If you have a variable number of "columns" (off/on), I'll guess that the
columns represent some attribute(s). I suspect that a table with only a
unique ID and 20 y/n columns is actually describing something else.

Another approach would be a tall/skinny design, perhaps something like:

ATable
ATableID
YourUniqueID (a FK to point back to which 'main' table this describes)
AttributeID (a FK from a lookup attribute table ... your 'about 20'
columns)
YourYesNo (the yes/no value)

This design would have one row for each "Yes", and, unless "no"s were
significant, no rows for no's.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Tim Ferguson]

considering speed,
storage and ease of use with queries.

What I fail to understand, well into the twenty-first century, is why
people think these are reasonable priorities for building a database
design. For me the list is like this:

1 Accuracy
2 Accuracy
3 Accuracy
.... (yes, the 515th entry is still 'Accuracy')


The cure for "storage space" is a bigger hard disk and for "speed" is a
faster server, and "ease of use with queries" is a decent UI. By the way
it's only by using R theory that all your queries actually remain
theoretically possible anyway.

the intended use is to simplify and centralize the data of multiple
access control systems.

the first column is a unique id.
the other columns are to hold as value 0(=off) or 1(=on).
the number of columns will typically be about 20; the number of rows
about 5000.

Why not about 100000 rows with three fields?

There will be several of such tables with varying numbers of columns.

Or several * 10E5 rows with four fields?

queries would be: "list all rows with actived (on) 4th and 5th fields"

No problem: it's just a SQL DIVIDE operation.

I don't see the problem here: make an accuate assessment of the entities,
then their relationships, then their attributes. Once you have a correct
model, you don't have to worry about the storage or access method --
that's the responsibility of the db engine.


B Wishes


Tim F

 

[Glenn]

thanks Jeff for responding

Glenn

If you have a variable number of "columns" (off/on), I'll guess that the
columns represent some attribute(s). I suspect that a table with only a
unique ID and 20 y/n columns is actually describing something else.

the columns simply represent entry points to a building and if the
keycard (=unique card) can open or not the door at that site.

not every site has the same numbers of doors, hence the variable part.
a simplication is to fix the number of columns to the largest number of
doors all sites confounded

Another approach would be a tall/skinny design, perhaps something like:

ATable
ATableID
YourUniqueID (a FK to point back to which 'main' table this describes)
AttributeID (a FK from a lookup attribute table ... your 'about 20'
columns)
YourYesNo (the yes/no value)

This design would have one row for each "Yes", and, unless "no"s were
significant, no rows for no's.

I'll try this to see it fits

best regards

 

[Glenn]

Thanks for your reply.

I understand you point of view although I don't share it.

first off, Accurary is the 0th rule, the one without everything else is
useless. So obvious and so inconceivable it could be otherwise.
If it didn't ring true the first time, repeating 515 times won't mend
it.

Secondly, it's not because you can add 'another' disk, 'another'
processor, 'another' box to the setup, you should not strive for a good,
elegant, beautiful solution.
that kind of thinking is why we now must use semi-supercomputers to get
some decent response on our wordprocessors.

best regards

 

[John Nurick]

a good,
elegant, beautiful solution.

That rules out any dialect of Basic!<g>

But if Access is the chosen tool, go for a tall narrow table.

 

[Glenn]

thanks for your reply
yes limiting the number of fields seems the way to go.