Some time ago, on a project long long archived, I philosophised a new database structure. Being of a sound-mind — and most importantly a one-man development team — I could run with this theorised new structure, which in essence is built on the idea of flexibility.
In a nutshell — everything is an item, items are defined by their attributes.
Items are anything. Blog posts, a person, a customer order. Each of these things have defining properties — a blog title, a name, an invoice number etc. Some have 400 defining properties, some have 4. Some are integars, some are 4000 words.
For a basic example, the structure looks like this...
In the small example outlined above, item_id=1 has 2 attributes assigned — a blog title and some blog content — while item_id=2 has just a blog title set.
This system (with some indexing and the occasional custom view) has served me well, allowing new attributes to be added — even if they apply to a single blog post, promoting a depth-over-breadth structure.
However (as always), structures are tested with queries. And this is where the strength of the flexibility was tested. Even something as simple as getting all the blog posts and printing them out as an archive would require multiple queries — ie. get all the items, cycle through and get the required attributes (if they exist). I've searched tirelessly over the past few years for a cleaner JOIN for these queries, but because this was my own theory, there wasn't an easily defined search term. I experimented with my own, but it was always faster/lighter to simply run with multiple small queries.
And then I stumbled on this (via The Straight Dope Forum), which I've been informed is similar to an Excel pivot statement...
MAX(CASE WHEN item_attribute.item_attribute_type_id=[attribute_id] THEN item_attribute.item_attribute_value ELSE NULL END) as [attribute_name]
For the example above...
SELECT i.*, MAX(CASE WHEN item_attribute.item_attribute_type_id=1 THEN item_attribute.item_attribute_value ELSE NULL END) as blog_title, MAX(CASE WHEN item_attribute.item_attribute_type_id=2 THEN item_attribute.item_attribute_value ELSE NULL END) as blog_content FROM item i LEFT JOIN item_attribute ON (item_attribute.item_id = i.item_id) GROUP BY i.item_id ORDER BY i.item_date_added DESC
Taking this further (spoiler: I did) the attribute part of the query can be drawn directly from the attribute table. An extra query for your page load (cached as needed, obvs) but will allow this main query to expand as new attributes are added.
There are some obvious scalability issues that I haven't yet tested (the current project I'm trialling this on only has about a dozen attributes so far). Not sure what kind of heavy load will be endured when there's 400 different attributes and all we need is a knife and/or 4 or 5 to display a bloody blog post on a page.
A possible solution could be a table that connects the specific query to the required attributes needed for that specific page. For example:
Continuing the original example, here we just need to first grab the specific attributes required for the blog_archive page (blog_post and blog_content) and then plug them into the query above. Later on, if we discover we also need to include the output of a new attribute called blog_image on this same page, we simply need to add the attribute and then connect it all up in the page_2_attribute joining table. Furthermore, this could even be expanded so the page_name is referenced automatically in the URL (settle down, sport).
As said, still very much in test mode, but the flexible structure and this new pivoting query is so far serving me well. But as Dave Winer would say "I'm still digging".
"If we think this is appropriate behaviour, heaven help us. I'm really sick of these posturing blokes thinking they can get away with stuff because they think it's funny. There are a lot of women who are sick and tired of feeling belittled and undermined. [...] He is a leader, people know who he is, and he can do a whole lot better than that."
"The board accepted Eddie McGuire's unreserved apology for inappropriate on-air comments he made last week. It also expressed its complete and ongoing support for his position as president. The board and the club take the matters raised by these comments seriously"
"If you're going to want to be treated equally, the point is, don't complain when it's too equal [...] The jig's up Caro, honestly and truly. You're becoming an embarrassment. And even if you were underwater, you'd still be talking [...] Those excrement who have weighed into this — I'd like to mention their names, but as nobody reads or listens to them because they're on second tier media outlets I won't bother. But if you spray excrement with perfume or put aftershave on a piece of excrement, at the end of the day, it's still a piece of excrement."
"As a public survivor of sexual violence, you'd think I'd agree with Rowe. But while I'm all in favour of McGuire donating money to a cause that stops violence against women, I wouldn't in a million years suggest that he donate it to White Ribbon.
Why? Because I and many survivors I know wouldn't give a single cent to White Ribbon.
The problem is that each time a celebrity commits or excuses violence against women, we watch the same predictable script unfold. Perhaps a high profile athlete has glassed his girlfriend or a media personality has sexually harassed a woman live on air. Then quicker than you can say 'opportunism', the White Ribbon collection plate will be whipped out.
But the question to ask is this: how much of that 'sorry' money is ever passed on to the support services that are assisting the victims in question?
And if White Ribbon is happy to pocket 'blood money' by cashing-in on individual acts of violence against women, then shouldn't they inject a hefty chunk of that money back into the frontline services that are expected to respond to the fallout caused by the initial violence? Services like domestic violence shelters, sexual assault counselling centres, or victim's legal aid — you know, the pragmatic services that do a little more than host 'awareness raising' breakfasts and the like."
"Medicare will never ever be privatised. Medicare will never ever be sold. Medicare is a core government service," he told reporters while campaigning on the NSW north coast. "It will always be delivered by the government and every element of Medicare's activities will continue to be delivered by the government."
"According to Labor, the Coalition has been planning to gut and sell off Medicare, claiming this has been part of the plan for every Coalition government. And to be fair, the facts have seemed to suggest this is the case. After all, Liberal leader Malcolm Fraser abolished Medibank after it was introduced by the Labor Whitlam government, then it was resurrected by Labor's Bob Hawke and renamed Medicare, then John Howard started giving rebates for those using private health insurance while cutting the Medicare rebate for doctors - the same trick that the Turnbull government has done, and which the Australian Medical Association is campaigning against."
"If you want to protect Medicare, you vote Labor in the election ... don't believe Malcolm Turnbull's words, look at his actions. It is a matter of record that they intend if re-elected to freeze GP rebates till 2020. That will mean that bulk-billing will become a thing of the past for many Australians. Mr Turnbull set up a privatisation task force and has commissioned the Productivity Commission to investigate options to privatise the delivery of human services by the federal government. Malcolm Turnbull has a plan to get rid of the bulk-billing incentive to pathology laboratories and diagnostic imaging services."
"There is absolutely no evidence at all that the Liberal Party has any desire to privatise Medicare. They've traditionally been a greater friend of private medicine but that's a completely different issue."
"It's great to have the opportunity to play an elite sport out on an elite arena in a professional environment. But more so, to look around today and see those little girls who will grow up knowing that's something they can achieve is amazing [...] I have to take myself out of the bubble and realise what an amazing revolution this is."
"In fact I reckon we should start the campaign for a one-person slide next year. Caroline Wilson. And I'll put in 10 grand straight away - make it 20. [laughter] And if she stays under, 50. [louder laughter]"
"This is why in the week where the AFL announced their first-ever women's league (yes!), and the Western Bulldogs and Geelong played in White Ribbon anti-violence match (yes!), we can't let the misogynist 'banter' of Collingwood president Eddie McGuire slip past."
"The first notable thing about this is, of course, that is is absolutely awful. These are some of the most high-profile men in football joking about hurting one of football’s most prominent women. So much of our discussions about violence against women acknowledge the importance of language and of attitudes in shaping the way men think about women. As the current government campaign says, 'violence against women doesn't just start.' While McGuire and co were undoubtedly joking, the underlying attitude is dangerous: it reenforces the attitudes of those who are willing to take their hatred of women beyond a 'bit of banter.'"
"These are the powerful men who get to be the mouthpieces of one of Australia's largest sporting communities. And where do they find their platforms? On equally culpable radio stations like Triple M, where a sea of men find favour despite not only holding misogynist views but in some cases having documented histories of violence against women."
"Words and jokes have incredible power. The fact that the comments were made on radio a week ago and were not called out is an indictment on everyone working in football. The fact that we can argue that this may have been done in jest shows a lack in understanding of this issue. I understand that the men who made these comments feel horrified that they could be construed as creating an environment that makes sexist behaviours or a culture of violence against women more acceptable. But the truth is these kind of comments do. The statistics and data say so."
"No journalist no commentator, male or female, should have to put up with personal attacks for doing so. McGuire avoided the issue at hand. He played the woman and engaged a couple of willing blokes only too happy to do his bidding."
Scripting.com - Your human-size life Dave Winer: "One of the biggest mistakes rich people make is to try to live larger than a single human being can. A mathematical impossibility. You can buy a big house, but you can only sleep in one bedroom at a time. You can own twenty fantastic cars, airplanes and yachts, but you can only be in one at a time. You can own an NBA team and a MLB team, and you get to sit in the nicest seat in the house at games, but you still can only sit in one seat. In other words, your humanity doesn't increase just because your wealth did. You don't get bigger."
Medium - Love Nature Jamie Maddison: "Chris Velten disappeared in Africa 13-years-ago, then he sent a Friend Request"
Medium - WestConnex. Is it a good project? No. Garth Ivers: "Eighteen billion dollars is the most cowardly number to spend on this project — far too much to achieve what it will, but too little to solve the problem. It purchases only the ability to claim you are doing something."
Scripting.com - America never was great Dave Winer: "What Trump really means is that he wants to make America white again. As it was before we had our first African-American president. It's a racist dog whistle. I should have seen it coming, we all should."