Table Structure(s)

[Gregg Bolinger]

In my application I am able to search Issues. On this search page I have different search critiera deemed the Search Filter. I want the user to be able to save a defined filter. So I am starting to put together my table(s) for saving the filter. My filter class contains the following members:

Long filterId - ID of the filter
String filterName - Name of the Filter
User user - User object of user that owns this filter
ArrayList status - A list of Status Objects (Open, In Progress, Closed)
ArrayList assignments - A list of User Objects that specify the assignment of an issue

So here is my initial idea for the table(s) to be constructed and I would like to know if this is the best way to go about it.

t_filter
------
id
name
user_id

t_filter_status
-------
id
filter_id
status_id

t_filter_assignment
-------
id
filter_id
user_id

So t_filter just contains the id and the name of the filter as well as the owning user id. t_filter_status would contain the different status' for a specific filter and the same for t_filter_assignment. So some example data might look like

t_filter
--------
id  |       name         |   user_id
1   |   My Assignments   |    9715
2   |   Last Year All    |    2345

t_filter_status
---------
id  | filter_id | status_id
1   |     1     |    2
2   |     1     |    3
3   |     2     |    0
4   |     2     |    1
5   |     2     |    2

t_filter_assignment
-----------
id  | filter_id | user_id
1   |    1      |  1234
2   |    1      |  3456
3   |    1      |  5678
4   |    2      |  2345
5   |    2      |  5609

Keep in mind that the status_id and user_id both come from their own tables respectivly. Also note that the reason that the relationship from assignment to filter is a many-to-one and the same for status to filter is because on the filter page I have 2 multiselect list boxes. One is to select the different status' that the user wants to view and one to select the different assignments the user wants to view.

So is this a good design approach? Does it make sense?

Thanks.

BTW - Here is a PNG of the schema if that helps illustrate this better.

http://www.embeddedthought.com/gregg/images/database.png
[ December 18, 2004: Message edited by: Gregg Bolinger ]

[Dilshad Marikar]

Hi

Have I understood your problem correctly:

Each user of your program can define a filter (or a number of filters), which can be given a name and saved. Each filter is created by setting two list boxes (where the | separates the different options):

Status: OPEN | IN PROGRESS | CLOSED
Assignment: List of all usernames

Assuming this is the case (I hope!) then I think this can be simplified to one table:

Table name: Filter
columns:
filter_id
user_id - user who owns filter
filter_name
selected_user_id - selected user
status - selected status


If status can only have a few values like OPEN/IN PROGRESS/CLOSED then I think it is easier just to represent them with numbers (eg 1,2,3) to store in the column which you can reference using constants. It may be possible to validate in your database that only valid values can be entered (eg Oracle can use check constraints).

[ December 18, 2004: Message edited by: Dilshad Marikar ]
[ December 18, 2004: Message edited by: Dilshad Marikar ]

[Dilshad Marikar]

and if you have a query like

select * from filter
where user_id = ?{user_id}

its probably a good idea to add an index to the user_id column to avoid scanning the whole table

[Gregg Bolinger]

Thanks for the reply. There is no | in the actual data. I was using that charater to seperate the columns in the representation. I don't like the idea of storing more than 1 possible value in a single column because that forces me to parse that out when I retrieve the data. Is that was you were suggesting?

[Dilshad Marikar]

Hi greg

No, i used | just to suggest separate the exclusive values in the list box. When it comes to storing this in my suggested table, the status would only hold one value.

Hmm, but if you wanted to select multiple statuses in your list box, rather than just one...you could use a bitwise operator? Like say if you selected OPEN and IN PROGRESS and they were represented by 1 and 2 respectively you could do a bitwise or (using | !) and store that in the column. And when reading it back you can use bitwise and to check what status option need to be selected. I refreshed my bitwise stuff here with good old java tutorial

Of course this assumes you only have a finite small set of status values. Otherwise you'll need a separate table (call it filterhasstatus). Hope this isn't complete nonesense...i'm very tired

[Gregg Bolinger]

Originally posted by Dilshad Marikar:
Hi greg

No, i used | just to suggest separate the exclusive values in the list box. When it comes to storing this in my suggested table, the status would only hold one value.

Hmm, but if you wanted to select multiple statuses in your list box, rather than just one...you could use a bitwise operator? Like say if you selected OPEN and IN PROGRESS and they were represented by 1 and 2 respectively you could do a bitwise or (using | !) and store that in the column. And when reading it back you can use bitwise and to check what status option need to be selected. I refreshed my bitwise stuff here with good old java tutorial

Of course this assumes you only have a finite small set of status values. Otherwise you'll need a separate table (call it filterhasstatus). Hope this isn't complete nonesense...i'm very tired

Well, it's not completel nonsense but I think you are just helping me decide to go with the method I suggested. As I stated in my original post..

Also note that the reason that the relationship from assignment to filter is a many-to-one and the same for status to filter is because on the filter page I have 2 multiselect list boxes.

[Dilshad Marikar]

oh dear, missed that multiselect word! Also i got a bit confused because i usually expect link tables to be named with XhasY, eg filterhasuser .

BTW thats a very polite way of pointing out i've been spouting complete nonesense

[Gregg Bolinger]

Originally posted by Dilshad Marikar:
Also i got a bit confused because i usually expect link tables to be named with XhasY, eg filterhasuser.

I have never heard that before. Do you have any online information regarding this naming scheme?

[Dilshad Marikar]

i'm not sure of anything online, its a naming convention we have at the company I work for. But we probably have dozens of link tables to name..

[Adeel Ansari]

Greg,
your idea sounds fine to me.

Dilshad,
and about the naming conventions. i do something like,

for many-to-many relationship:

TEACHER
TEACH_COURSE_REL
COURSE

for one-to-many relationship:

TEACHER
ADDRESS_REF

But what mostly seen is use underscore seperated ALL CAPS in your table names, column names, constraint names, probably in your all database objects. may be i have seen this because i have worked on oracle most of the time.

thanks