How to insert an Array/Objet into SQL (bestpractice)

I need to store three items as an array in a single column and be able to quickly/easily modify that data in later functions.

[---YOU CAN SKIP THIS PART IF YOU TRUST ME--]

To be clear, I love and use x_ref tables all the time but an x_ref doesn't work here because this is not a one-to-many relationship. I am making a project management tool that among other things, assigns a user to a project and assigns hours to that project on a weekly basis, per user, sometimes for weeks many weeks into the future. Of course there are many projects, a project can have many team members, a team member can be involved with many projects at one time BUT its not one-to-many because a team member can be working many weeks on the same project but have different hours for different weeks. In other words, each object really is unique. Also/finally, this data can be changed at any time by any team-member - hence it needs to be easily to manipulate. 开发者_如何学运维

Basically, I need to handle three values (the team member, the week we're talking about, and how many hours) dropped into a project row in the projects table (under the column for project team members) and treated as one item - a team member - that will actually be part of a larger array of all the team members involved on the project.

[--END SKIP, START READING HERE :) --]

So assuming that the application's general schema and relation tables aren't total crap and that we are in fact up against a wall in this one case to use an array/object as a value for this column, is there a best practice for that? Like a particular SQL data-type? A particular object/array format? CSV? JSON? XML? Most of the app is in C# but (for very odd reasons that I won't explain) we could really use any environment if there is a particular one that handles this well.

For the moment, I am thinking either (webservice + JS/JSON) or PHP unserialize/serialize (but I am bit sketched out by the PHP solution because it seems a bit cumbersome when using ajax?) Thoughts anyone?

Hmm it sounds like you should rethink your schema, If I understand you why don't you just create one like this:

Users
  ...
Projects
  ...

WorkItemTracking 
  UserId
  ProjectId
  Week // between 1 and 52, or a datestamp
  Hours // number of hours budgeted for this task this week

To find what the user is doing for the next 3 weeks,

SELECT * form workItemTracking where userId = <id> and week between <weekstart> and <weekend>

This will give you the ability to assign multiple users to multiple projects, and projects to multiple users. It also makes updating easy. Does this make sense?

Could you flatten the array into a text column and use some special token to separate each piece of data, for example the string Joe/03/25 would be an array someArray={'Joe','03','25'}. Would that work?

I admit there could be problems with this approach. For example, if you want to search this column for "Joe" you'd have to use ...LIKE 'Joe%'. Doesn't seem very indexable, so your queries will be a little slow.

Some databases such as Oracle actually allow array types as column types (I think these are also sometimes called Nested Tables), and I think (could be wrong though) that you can search and index the elements of an array in a column. You didn't specify which database vendor you were working with, so it's possible that you might be able to get this as a proper array, depending on which technology you choose.

For the sake of anyone else wondering...

"Damnit Byron you got me; I hate looking stupid on the interweb haha. But you're right - that really is the better solution. THANKS!"