CODEWITHSUNDEEP
databasedatabase-designdatabase-schema
GBleaney
GBleaney

Reputation: 2196

Best way to structure a database where one row has to map to many rows in another table?

I am trying to create a database that will store a table of Devices and a Table of actions. Each device will be associated with multiple actions. There will be a lot of overlap between actions. For example, both an iPhone and Android phone would have basic actions such as "Shake", "Swipe", etc. To avoid creating many duplicate actions, I was planning to introduce a third table that maps Devices to Actions via their IDs. The whole setup would look something like this: enter image description here

Is this the best way to do it? Or is it more efficient to just duplicate the actions? Any suggestions of other ways to do it?

Upvotes: 1

Views: 125

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

As Matt already noted, your current schema makes sense if the relationship is many-to-many. It sounds from your description as though this is the case.

Here is one change I would suggest: The "other columns with more info" header should appear in the Devices table and the Map table as well as the Actions table. Info that pertains to devices only would show up in the Devices table. Info that pertains to Actions only would show up in the Actions table.

And info that pertains to a given action only in the context of a given device would show up in the map table.

Map is a good name for the middle table. However, if you start looking at the literature on database design, you'll find it's often called a link table, and association table, as junction table, or some variation of one of these.

Upvotes: 0

Matt Houser
Matt Houser

Reputation: 36073

It depends on the rest of the data.

If the "Shake" row for the iPhone will be different than the "Shake" row for Android (when looking at the rest of the data), then you might as well use a foreign key fro your Actions table directly to your devices table and skip the mapping table.

But if your Actions and devices truly will be many-to-many, then your current schema makes sense.

Upvotes: 2

Related Questions