Category redesign

[squirrly]

I'm struggling on how to build/rebuild these new tables.

I've currently got five categories that users subscribe to:
CENTRAL
WEST
EAST
(etc.)



I want those categories to be standard with sub-categories that users subscribe to:
CENTRAL
Item1
Item2
Item3

WEST
Item1
Item2
Item3

EAST
Item1
Item2
Item3

(etc.)

I will manually propagate current subscribers into all sub-categories accordingly once I'm done with rebuilding tables, but I'm stuck on how to create the new tables and relate them for the web.

[dblife]

Maybe, a table for areas, central, east west etc.
A table for items.
A table for joining them (junction table)
Let me know if you need any further explanation.

[squirrly]

I thought about that, but need yes/no fields for each sub-category item and will relate my current user table. So a simple but tedious way would be to put everything into one table resulting in 36 columns.

SubID
UserID
CENTRAL Item1 - yes/no
CENTRAL Item2 - yes/no
CENTRAL Item3 - yes/no
WEST Item1 - yes/no
WEST Item2 - yes/no
WEST Item3 - yes/no
(etc.)

Or I could separate the tables into six main categories with six subcategories having yes/no fields and relate the user table to all:

tbl_CENTRAL
CENTRALSubID
UserID
CENTRAL Item1 - yes/no
CENTRAL Item2 - yes/no
CENTRAL Item3 - yes/no

tbl_WEST
WESTSubID
UserID
WEST Item1 - yes/no
WEST Item2 - yes/no
WEST Item3 - yes/no

(etc.)

There does not seem to be an easier way since the yes/no fields are required.

[RayMilhon]

As Previous proposed you need 3 tables.

tblArea
areaID
AreaName (East, West, Central....)

tblUser
UserID
UserData (Not sure what your data elements are but name, address are pretty standard)

tbl_Data
UserID Foreign Key to User table
AreiaID Foreign Key to Area Table
Item1 Data Elements As many items as required.
Item2
Item3
.....

[dblife]

Cannot say I understand what you are trying to do here but whenever I find myself with an abundence of yes/no fields, it usually means my design is not good.
If we knew exactly what you were trying to achieve it might make it simpler to help.
Without insulting your abilities, serious Access developers tend to steer clear of 'yes/no' boxes for anything at all - they do not handle nulls well and it creates problems during front end creation. They tend to be a hangover from spreadsheet design.
You may find, if you explain what you are trying to do, we could help make the design easier?

[squirrly]

I have a lost/found pet website. My users are currently subscribed to these categories - CENTRAL, WEST, EAST, NORTH, SOUTH, NEWSLETTERS. They can simply subscribe or unsubscribe by clicking the corresponding checkboxes online.

I want to extend the subscriptions in each category - Found Cats, Found Dogs, Lost Cats, Lost Dogs, etc., so users can subscribe/unsubscribe to each new subcategory via corresponding checkboxes online.

I'm redesigning a flat database to a relational database and my brain is stumped at how to relate these new tables without using a yes/no field for every item. I'm still fairly novice at this..... I understand the relationships, but am still not sure how to go about the yes/no field problem and almost need a diagram.

[dblife]

bless.. i can help with the table design.
i am useless when it comes to web servers.
my only involvement is 'mysql' which is a discipline in itself and is only one approach of many..
i can help you build the pet rescue db table structure and maybe someone else can assist with uprating to web based.. (sql is a standard language which can be applied to a great many table structures)

[squirrly]

Thanks... I've already rebuilt the entire database, I'm just now editing the subscription portion. My site has been live for years so I know how to do all that. I'm just stuck in thought of how to create these subscription tables....

I'm currently reading up on some articles about how to store checkbox data, so maybe something will trigger an idea. I'm thinking I'm going to have something like this:

tbl_Users
UserID (pk)
UserEmail

tbl_SubscriptionArea
AreaID (pk)
AreaName

tbl_SubscriptionType
TypeID (pk)
TypeName

tbl_Subscriptions
SubID (pk)
UserID
AreaID
TypeID
Subscribe (yes/no)

Running a query on this produces the correct data. The only thing is, if a user is subscribed to six different things, it will create a SUPER long table!!!

[RayMilhon]

My Recommendation is use a Byte Field then the set the 1's and 0's to subcription or not

10110000 = Subscriptions to newsletters 1,3,4

You can also use a numeric field where the the byte is translated so 13 would = subscriptions to newsletters 1,3,4

since you have 6 different newsletters you would have 2^6 (64) possible combinations. How you translate those to specific subscriptions is the tricky part. Let me think on that part for a bit and I'll get back to you later today.

[RayMilhon]

Here's how to retrieve Whether a user is subscribed to a particular Newsletter

Newsletter List sample

NewsletterID Desc
1 Lost Dogs
2 Found Dogs
3 Lost Cats
4 Found Cats

Since there are 4 possible values we have 2^4-1 possible combinations since 0000 would be zero subscriptions and would be an invalid choice

0001
0010
0011
0100
0101
0110
0111
1000
1001
1010
1011
1100
1101
1110
1111

Since this user subscribes to 1 and 2 the numeric value 3 would be passed to the function

Lngsubscriptionlist = 3 Or 0010
Lngnews = 2 for Found Dogs

So we are checking for the second value We loop through each bit exiting when we hit the one we are looking for.


Public Function IsSubscribed(ByVal lngsubscriptionlist as long, lngnews as long) As Boolean
Dim rslt as int
Issubscribed = False
For lp = 1 To lngnews
Rslt = Lngsubscriptionlist Mod 2
Lngsubscriptionlist = lngsubscriptionlist \ 2
next
If rslt = 1 Then IsSubscribed = True Else IsSubscribed = false

Function End

Setting the Numeric value would require that at the point the record is saved. check each checkbox on the form and adding that bit. For example: there are 4 checkboxes for 4 possible subscriptions. check them in order and set the bit

If ckbox1 = checked then
lngvalue1 = 1
Else
lngvalue1 = 0
End if
If ckbox2 = checked then
lngvalue2 = 2
else
lngvlaue2 = 0
End if
If ckbox3 = checked then
lngvalue3 = 4
Else
lngvalue3 = 0
End if
If ckbox4 = checked then
lngvalue4 = 8
Else
lngvalue4 = 0
end if

Subscriptionvalue = lngvalue1+lngvalue2+lngvalue3+lngvalue4

I'm sure there is a better and more efficient way I just haven't had a chance to mull it over yet. If I think of something I'll post it here later.

[squirrly]

Thank you, I will study what you've provided and read up. I'm inclined to take the easiest way out with yes/no checkboxes - the same as what will be on my web form, but understand that's not how it should be done if done properly.

[RayMilhon]

You're welcome however I do respectfully disagree what may seem the easiest when you're at point A may end up being a nightmare when you get to point Z. Been there done that.

[squirrly]

I gotcha...!
I've heard about the instability of using multiple checkboxes, but what IS the instability? What happens?

[RayMilhon]

Check boxes actually have 3 possible states not 2. Checked, Unchecked and Null The instability comes from not recognizing and accounting for the possibility of nulls

[RayMilhon]

Radio Buttons are better if you want to limit it to Yes/No Values.