DBDesign - Private/Public data

[danielgoehl]

Hi everybody!

I am Daniel and I am developing a Alumini Database right now using MySQL and Ruby On Rails. First of all, I must say this is an amazing forum. I really enjoy reading the posts. I hope you will be able to help me.

I have the following problem in my design. Every alumni will have certain properties, such as Adress, Study, Birthdate, Employers . . . you get the picture. Now the Alumi should be able to select which data are publicly viewable and which only by other alumni.

There is a sulution in place, which I inherited, but I would like to change it. The old solution is to have one large Table where there is an extra binary column for every data column. -> Adress, Adress_Public

Well this is not optimal. Now I thought of having a properties table and a properties_public table in which I could save the memberID and the property that is public.
But I do not really know how to implement without havong to loop over all the findings.

If I would, with the above scheme, select all properties I would get

1, Adress 
1, Birthdate
1, Job

And then I need to loop over this to get the actual information. That is not really effective either.

Do you maybe have an idea?

Thank you very much!

All the best

Daniel

[kb.net]

Hi,

We lately started adding all the allowed information to be view in 1 field for each user as a concatunated strings seperated by a special char like ";" or "/" then we check parts of it using string functions like mid, IN, find, search or substr depending on language being used the table usually looks like:
1 Name;Address;Phone
2 Name;Phone

I hope this helped.

[danielgoehl]

Hi thank you very much for this tip!

This looks quiet good. As far as I understand, I still need to make two querys to get only the public data as a result. I first would select the public columnames and the build a query with them to receive the actual data.

Isn't there a possibility to put the whole logic of this into the DB so that a SELECT only gives me publicly viewable data?

I am busting my head about this, but I can't find a solution.

Thanks

Daniel

[kb.net]

Hi,
What are u using as an interface VB6, .net, access ... ? depending on that I may be able to provide you with the solution.

[danielgoehl]

Hi!

I am using Ruby On Rails together with MySQL. But I know some VB. Some pseudo code would really help!

Thanks