Delimited fields or unused fields? Design issue 3

[Leozack]

Hi - I have a table which has, for instance, 5 fields and maybe people only need the first one to be filled in often. So 4 might well be empty. Is it better to join them into 1 delimited field or leave them as their own fields?
And - if I have some fields that are only relevant to some types of rows, is it better to have 1 field for them delimited, or have them as separate fields? (the same issue there really)
The other side of it is - is it better to split such delimited fields in the mysql call, or split them afterwards in php?
I might know which is easier though I can do it either way now I've researched, but I don't know which is better. I also don't know how big this db will get or how often these fields won't be used so it comes down more to the overall design issue of which is better. I'm thinking efficiency, speed, db load, and so on.
Thanks for all thoughts

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

Ok I've just spent about 5 hours failing to do what I need to do to use the table structure suggested on here for the name fields. This query works fine

SELECT adverts.*, globalservice, deal, category, hours, startingbid, increment FROM adverts
INNER JOIN id_globalservice ON adverts.globalservice_id = id_globalservice.globalservice_id
INNER JOIN id_deal ON adverts.deal_id = id_deal.deal_id
INNER JOIN id_category ON adverts.category_id = id_category.category_id
LEFT JOIN auctions ON adverts.id = auctions.parent_id

The auctions table needs left join not inner else only rows with auction data are returned.
But assuming my new table called names which has "id", "parent_id" (foreign key), and "name" fields (and remember that there my be 1-4 rows with the same "parent_id" key (the old name 2-5 fields) then how can I include these in the results? I've tried all sorts of things for hours now and all sorts of researhing online and failed to do it with trial and error.
I know it probably involves adding the same names table on 4 times as n2 n3 n4 n5 etc and sure enough I can get every row of data with a different name column for each of those but I need to combine all the names for 1 parent_id into the main parent row so I only get 1 row per main table row which includes a name1 name2 name3 field etc that contain the info from the names table if there is any.

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[r937]

GROUP_CONCAT function

and before you say it, yes, i do realize that the end result looks exactly the same as though you had kept the original 5 side-by-side fields, or a single field with comma-separated list

but the way, you will need a GROUP BY clause, and this means you need to itemize your columns, not use "select asterisk"

r937.com | rudy.ca

 

[Leozack]

Seems I have mysql 4.0.18 so no group_concat, which would explain why it was breaking when I tried it.
You mean with the names table having that "no empty cell" approach of one-to-many from the main table, there is now way to get the rows from the names table which have matching parent_id's to the maintable's id's to appear in the rows as seperate columns lik ename 1, name 2, etc? Looking at the group_conctat documentation and from what you said you make it sound like they'll be returned as 1 field delimited?
Is there a way round this without upgrading mysql? And is it possible to get them returned just as if they were name 2 name 3 etc columns of the main table?

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

And I also hate to reask but ... what to index? See my previous post. If I can't sort these out I'm stuck after all this modification ><;

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

Come on guys I've done everything you said yet tehre are 2 things blocking me.

1 - I need the extra names, be it 2 or 4, to be returned per row preferably as seperate fields not as a delimited field. This must be possible before group_concat was around as that's only for the unwanted delimited string option anyway.

2 - I'm not sure which fields to index. They say it should be the fields you join with or specify in your where clauses so maybe I'll just see when I've finished the code.

But right now, I'm stuck. I've spent ages playing around trying to make a proper join rather than a cross join out of the name table 4 times over, to only yield results for each of the 4 columns once for each name per parent id, and had no luck.

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[r937]

i can't help you any more

i gave it my best shot, really i did

good luck with your code

r937.com | rudy.ca

 

[Leozack]

Ok so there was no way to group_concat before mysql 4.1?
And there is no way to get a column from a bunch of rows that share the same value in another column (parent_id) to be returned in 1 row? That's mind numbing.

Ok - seeing as the nice idea of a name table having infinate rows per main table row is prooving impossible to join up, should I just use 1 row with 4 fields per main table row, or 1 row with 1 delimited field? Which I think is the original topic of this ... looong thread

_________________________________
Leozack

MakeUniverse($infinity,1,42);