1

I've got 'Lists' that contain different objects sometimes with similar class, sometimes with different class.

So I have a table 'lists' to store a list. I have a table 'strange_objects' to store a strange object and a table 'regular_object' to store a regular object.

Now I want a list to have several objects in order. What schema would make the most sense?

I was thinking of the list having list_objects (with an order, type and type_id) to reference the object at that position in the list. However I am a bit unsure if this would be a nice structure as I always need to do a join if just want to fetch all the objects for a list or to get all 'regular_objects' in my list.

What structure would you advice to store my objects?

What I was thinking of is the following (FKs were left out for now)

-- Create syntax for TABLE 'list_items'
CREATE TABLE `list_items` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order` int(11) NOT NULL,
  `list_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Create syntax for TABLE 'lists'
CREATE TABLE `lists` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Create syntax for TABLE 'regular_objects'
CREATE TABLE `regular_objects` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `list_item_id` int(11) DEFAULT NULL,
  `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `question` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Create syntax for TABLE 'strange_objects'
CREATE TABLE `strange_objects` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `list_item_id` int(11) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `data` mediumblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  • This is a little vague. Are you just talking about line items on an invoice? – Robert Harvey Jun 19 '18 at 18:51
  • Sort of, but line items may be very similar in nature, the regular_object and strange_object have different properties. – Roderik Jun 19 '18 at 18:56
  • What properties? – Robert Harvey Jun 19 '18 at 18:57
  • E.g. StrangeObject: {'image_id': 5, 'description': 'my image'}, RegularObject: {'credits': 3675, duration: 8} – Roderik Jun 19 '18 at 18:59
  • Are these products, sprites in a game, or ... ? – Robert Harvey Jun 19 '18 at 19:00
0

I think you are close. I would have lists, with list_items as a bridging table and the objects.

The bridging table would have the list_id, item_id and order. It should also ideally have a unique and separate primary key. This approach would allow for objects to be re-used in different lists. Or even multiple times in one list.

The downside is that you couldnt have a foreign key constraint on the object tables unless you had a field for each in the bridging table.

Another possibility is to combine the object tables and differentiate with a flag. Columns can be null if the attribute doesnt apply. That should be pretty simple unless there are dependencies or rules between the object types.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.