Design Advice Sought 1

[SPrelewicz]

I am redesigning the structure of our site at work and am stuck at one part of the design.

I have a table for accounts, which has a column called affiliation #. The affiliation # joins with the affiliations table, which has columns for name, IP, contact, etc. Then I have a data table which stores all the content available. The user gets certain content based on their affiliation. The only design I can think of is to list each content item multiple times with another cell for the affiliation number.

IE:

affiliations
ID name

1 RIA
2 ECMC
3 HRC

content
name IDs_that_subscribe

content1 1
content1 2
content2 3
content3 1
content3 2
content3 3

Does this make sense? I feel there's got to be an easier/more efficient way, but am not experienced enough to come up with it. I thought of doing it where each content item was listed once, with multiple columns listing all the subscribes affiliate numbers like:

affiliations
ID name

1 RIA
2 ECMC
3 HRC

content
name IDs_that_subscribe1 ID_subscribed2 ID_subscribed3

content1 1 2
content2 3
content3 1 2 3


but I'm sure this is sloppy. Any ideas for me? Thanks!

Scott

 

[RPAtl]

Definitely do not do the multiple columns--that would not be an efficient design. The only recommendation I would make is to set up a third table of Contents with a ContentId

ContentId Content
1 Content 1
2 Content 2
3 Content 3

Then revise your second table to just have the two id's that relate:

ContentId AffiliationId
1 1
1 2
2 3
3 1
3 2
3 3

This should be pretty efficient. Then just use queries to get to any data that you need. Hope that helps.

 

[SPrelewicz]

Fantastic! Just what I was looking for, thanks SO much!

Now let me get greedy and ask for a little bit more advice.

What is the general rule, if any, of how many columns are okay? Or is it as many as possible as long as the data fits coherently.

IE, I have another accounts table with all kinds of info:

username, password, first, middle, lastnames, email, tele, department, last_login, last_lookup, affiliatio #, ISP, verification_status. This all seems to fit together coherently as a "person Object" so to speak. Couls/should this be broken down further or am I being paranoid?

Wow, thanks again!

Scott

 

[RPAtl]

You can have as many columns as you need, but typically there just won't be that much data that truly relates just to one key field. As an example, or suggestion, rather than having a last_lookup field, make a separate table that has all history of lookups (I'm assuming that's a date, but it could be anything):

PersonId LookupDate
1 10/11/2002
2 10/15/2002
1 10/16/2002
1 10/18/2002

Then if you ever need last_lookup, just query that table for the highest date for the given personid. This allows you to maintain history if that would have any use to you.

As for the others, as long as each data item is truly a one-to-one relationship with a person object, I don't see any harm with putting it in one table. If it gets to big or performance is affected, maybe you could break it into different tables by category (ContactInfo, SystemsInfo, etc.) and just make sure to keep the same id/key across all tables so you're never storing anything (like Name) twice.

 

[SPrelewicz]

That answers alot of questions for me, thanks again! It will help for the entire project, and I do appreciate it!

BTW, you are correct, it is a date, and I will do it that way.