Menu
Community /Pin to ProfileBookmark
@NogDogMay 04.2022 — #
This sounds a bit too fragmented to me (but based on my limited understanding). I'd be more inclined to have a "type" table, an "object" table...and then think about the "reviews" table later. ;)
So,
Then as/when you need to add a type, it's just a new row in an existing table -- no need to add more tables. Reviews in your
If I needed to track history, I'd probably just (just?) timestamp each addition to the
Or something like that, just trying to give some food for thought.
Editorials
This is my a best practice, how should I approach this one question… it seems messy.
**Scenario
I have a range of object types that I’m waiting to get user reviews on, it’ll be in the form of a textual summary. Each object type has a list of individual instances of the type. Each object type has it’s own table specific to it and each instances has a row in that table.
**Example
Type: Cars
Car 1
Car 2
Car 3
Type: Buses
Bus 1
Bus 2
Bus 3
**What I want
I’m trying to work out how to manage this in a database and from a administrative point of view. Given it’s the same principal for all object types (10 in total) I thought a centralized database table and admin panel would be easier than 10 versions.
I’m thinking a SQL table, let’s call it objectReviews
It would have columns, objectTypeId (1 = cars, 2 = buses), objectId (the index id of the individual object ).
**My Question / Issue
This is a evolving review, a bit like a Wiki page but much smaller. If object type 2, instance 3 (3rd bus) has a review. What can I do when if a user needs to update it?
**Question / Ideas / Thoughts
– Should I have a history table, move the old one over to the history table and insert a new one?– How does this work for minor updates or updates which are the same? (I’m thinking user credits)– If a admin needed to ‘approve’ a change, how could you stop updates on top of an already pending update
Happy for anyones thoughts and opinion
Sign in
to post a comment1 Comments(s) ↴
0
@NogDogMay 04.2022 — #>@kiwis80#1643803 Each object type has it's own table specific to it and each instances has a row in that table.
This sounds a bit too fragmented to me (but based on my limited understanding). I'd be more inclined to have a "type" table, an "object" table...and then think about the "reviews" table later. ;)
So,
type[code=text]
id name description
-- ----- -----------------------------
1 car Everyone knows what a car is
2 bus Almost everyone knows what a bus is
3 bike Human-powered 2-wheeled vehicle
[/code]object[code=text]
id type_id name description
-- ------- -------------- --------------------------
1 3 Schwinn XYZ123 Some generic bicycle
2 1 Porsche 911 Classic sports car
[/code]Then as/when you need to add a type, it's just a new row in an existing table -- no need to add more tables. Reviews in your
reviewobject_idjointypeIf I needed to track history, I'd probably just (just?) timestamp each addition to the
reviewstatus[code=sql]
select review.title, review.text, object.name, object.description, type.name
from review
inner join object on review.object_id = object.id
inner join type on object.type_id = type.id
where object.name = 'Porsche 911' and r.status = 'A' -- for Approved
order by review.submit_time desc
limit 1
[/code]Or something like that, just trying to give some food for thought.