Database Structure

How to model networks

[beaudeal]

Hi Everyone,

I want to create a social network that seperates users based on networks (similar to the way facebook works). However on this site each network will have drastically different profiles so I cannot save everyones information in one Profile table. The only way I can think to do this is to have a general User table with generic stuff that applies to everyone and then a profile table for each network that will have a key of UserId. However I don't want to do this because I don't want my profile queries to go find the person's network and then select the proper table, etc, etc. So heres my question: what is the best way to structure these tables. Thanks so much in advance!

Alex

[justageek]

However I don't want to do this because I don't want my profile queries to go find the person's network and then select the proper table, etc, etc.

Why? This would work just fine.

JAG

[beaudeal]

it just seems to me that this is relatively inefficient -- especially if there were to be 100 or 1000 different networks. so i suppose it might work fine but i feel like theres a better way and i'm hoping somebody will have some suggestions

thanks for the input though

[justageek]

it just seems to me that this is relatively inefficient -- especially if there were to be 100 or 1000 different networks.

It'll work just fine for a long time. Joing the main table to another table of networks would be relatively painless for a long time.

so i suppose it might work fine but i feel like theres a better way and i'm hoping somebody will have some suggestions

I won't argue that. There are some pretty fart smuckers here.

JAG

[Lilliabeth]

I'm good at data modelling, but I don't know anything about social networks. Could you be more specific? Much more specific - give examples of fields, please.

[beaudeal]

JAG, thanks for the input again ;)

Lilliabeth -- here is an example:

I have 10 "networks" that can be joined when registering with the site. They are called networks 1,2,3,4...10. Each member is automatically put into the User table with the colums UserId, Network, Name, Email, Password. Each member is given the option to create a profile, but each network has different criteria.

Here is the only way I can think of doing this (which I think is very inefficient):

I have created 10 tables called Profile_Network_1, Profile_Network_2, Profile_Network_3....Profile_Network_10. The fields are different for each but all contain columns called ProfileId and UserId. For example, Profile_Network_1 has the columns ProfileId, UserId, A, B, C; Profile_Network_2 has the columns ProfileId, UserId, D, E, F...etc etc.

When they fill out their information and create the profile, the query checks the network in the User table, then has to find the right Profile_Network_X table, and inserts the data. When retreiving the information a query gets the Network from the User table, finds the right Profile_Network_X table, and retrieves the information.

I just feel like there has to be a better way. If this isn't clear let me know and I'll try to re-explain.

Please let me know what you think. Thanks so much!

[Lilliabeth]

Again, I ask for forgiveness that I am ignorant about what categories of info you will track for this type of project, but I think you should consider...

Networks table: NetworkID, NetworkName

Users table: UserID, UserName, Email, Password, NetworkID

CriteriaTypes table: CriteriaTypeID, CriteriaName (the CriteriaName field would be populated with the data you were considering using as field names...A, B, C, D, etc - these are values, not fields)

UserCriteria table: UserID, CriteriaTypeID, Criteria

Now, all criteria are stored in the same field... one place to query, that's a good thing.

Will that work or is there something important about this type of network that I don't "get"?

[beaudeal]

I'm trying to visualize this in my head and it seems to me like it is what I am looking for with one exception -- In the UserCriteria table how do i specify the data type of Criteria -- because it will vary depeding on if it is a date, a text field, a varchar, an integer, etc...

thanks for your help with all of this, it is greatly appreciated!

[edited by: beaudeal at 3:42 am (utc) on Feb. 1, 2007]

[Lilliabeth]

Check your sticky mail; I sent my email address to you.

[Lilliabeth]

You're right, you'll need different fields for each data type. I don't know what those fields would be though, since I don't know anything about this type of network.

[beaudeal]

so then how is that going to work? I can't quite grasp how to fit that factor in...

[Lilliabeth]

Check your mail. I sent a sketch.