2 Replies - 374 Views - Last Post: 19 July 2013 - 06:34 AM Rate Topic: -----

#1 Zerbruz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 19-July 13

Database design

Posted 19 July 2013 - 06:15 AM

I am having trouble coming up with a database design for my program since databases isn't my strong side.

Users are able to create lists containing chosen words (list of words) from a different table containing every word (dictionary).

The List
Will just consist of an ID and a name of the list.

List Contents
Will contain all words used only in the list.

Dictionary
Will contain every word available.


EXAMPLE

ALL available words in Dictionary:
Apple
Orange
Banana
Brick
Stone
Car
Flower

The list "Fruit" contains:
Apple
Orange
Banana

The list "Mixed" contains:
Orange
Flower
Brick

What would be a good design to accomplish this?
Are there any tutorials for this type of implementation?

Thank you!

Is This A Good Question/Topic? 0
  • +

Replies To: Database design

#2 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: Database design

Posted 19 July 2013 - 06:29 AM

This looks like a fairly straight forward Many-To-Many (N:M) relationship.

The dictionary will list all the words, each row having a numeric ID and a string field for the word itself. The list table will be much the same, except the string field will be for the list name. - The List contents table would be what we refer to as the junction table; the table that links the list table and the dictionary. It will contain two integer fields, one to store the ID of a list, and one to store the ID of a word.

The design might look something like this:
Dictionary      ListContents             List
+---------+     +------------------+     +---------+
| id (PK) |1-|  | list_id (PK, FK) |*---1| id (PK) |
| word    |  |-*| word_id (PK, FK) |     | name    |
+---------+     +------------------+     +---------+



This is an example of the data those might contain:
Dictionary
+-----+--------+
|  id | word   |
+-----+--------+
|   1 | car    |
|   2 | apple  |
|   3 | orange |
|   4 | bike   |
+-----+--------+

List
+-----+----------+
|  id | name     |
+-----+----------+
|   1 | veichles |
|   2 | fruits   |
+-----+----------+

ListContents
+---------+---------+
| list_id | word_id |
+---------+---------+
|       1 |       1 |
|       2 |       2 |
|       2 |       3 |
|       1 |       4 |
+---------+---------+


Was This Post Helpful? 2
  • +
  • -

#3 Zerbruz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 19-July 13

Re: Database design

Posted 19 July 2013 - 06:34 AM

Thank you for your help!

I was unsure on how to design the actual list contents. I never thought about using it as a link between the two tables, this was very helpful, thank you.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1