0

In this database I have "accesses" - each access can have any number (usually less than 4, sometimes as many as 15) attributes. I also have "tickets" where N users are requesting M accesses, so I've got a sub-ticket table with the NxM "user access requests". Then for the sub-ticket, there is another table with the A attributes for that access (where "A" varies depending on the request).

enter image description here

So, for example, if I have two users requesting three accesses, and those accesses have 2, 3 and 4 attributes respectively, I have 1 ticket record, 6 sub-tickets records, and 18 sub ticket attribute records.

The problem is that I want to quickly group the sub-tickets together, so that when I display them I can find all the ones for one type of access, then all the ones of another type of access, and then the third type of access. I know, each type of access should have been given a unique id somehow, but they weren't, and now it's my burden. I only am allowed to "suggest" database changes, not insist on them.

Any suggestions?

Improve this question
3
  • 1
    What comprises a "type of access?" A specific collection of attributes? Is it defined "at run time" by the attributes of the ticket when the access is asserted, or is it predefined?
    – Robert Harvey
    Mar 27, 2014 at 21:14
  • There is another group of tables that determine what combination of attributes constitute a valid "access request". For some systems, it might just be a system name, on others it drills down to system, database or system, file share, permissions, etc. I don't recall all the bizarre combinations of attributes other than that it normally has fewer than 4 of them and sometimes has as many as 15.
    – Paul Tomblin
    Mar 28, 2014 at 13:54
  • Not being primarily a database person, I'm trying to understand the question. In something vaguely similar that I did ages ago, there was one database that described the structure of another database. Then, since the first one changed at low frequency, there was benefit in precompiling it.
    – Mike Dunlavey
    Apr 3, 2014 at 23:47

2 Answers 2

Reset to default
1

We have a large (100 million row) tables which hold variable number of attributes per item too. For ad hoc querying I have created views which pivot the data i.e. turn rows into columns. The result can then be queried as if it were a "proper" table. You'll have to know the attribute names for this to work, however, or write a script which dynamically creates the SQL to create the view.

Improve this answer
0

After making some test data to show the problem, I've managed to convince the powers that be that since I know which "sub ticket attributes" belong to which "access" at ticket creation time, it would be better just to put a "access serial number" (asn) field into the sub ticket table so if I have N users and M accesses, I'll have N subtickets with asn "1", N with asn "2" and so on.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.