0

i am thinking of a way to design my database but i want to do it right, which in my opinion includes:

  • the DB design should not have any redundancies
  • the design by itself should set the limits for what is possible and what is not
  • the DB design should not need any business logic to achieve the persistance of the objects

This is where i am now: i have a design in my brain and want to transport that into mysql (any other DBMS would be fine but i don't think this is the issue). Afterwards i want to build a java application on top of the database architecture.

My Problem seems rather simple (for simplicity i removed many things):

i have two distinct objects/tables:

  1. Ships
    • id
    • name
    • speed
  2. Weapons
    • id
    • name
    • damage

What i want to do is to build something like a tech-tree:

  • if you want to build Ship "Y" then you have to research Ship "X" first.
  • if you want to use Weapon "B" then you have to research Weapon "A" first.
  • if you want to use Weapon "C" then you have to research Weapon "A" first.

This would be pretty easy but now to the next constraints:

  • if you want to build Ship "Z" then you have to research Ship "Y" and Weapon "B" and "Weapon "C" first

Here is a visual representation of the tree:

Tree

To represent a tree i came up with only one solution:

create a new table called "entities" with the fields "id" and "table_name"; also create a m2m-table which has two fields "id_entity" and "id_entity_prequisite"; the tables "ships" and "weapons" do not have a real primary key "id" anymore, but instead use a foreign key to the table "entities->id"

  • good: the full tree can be represented
  • bad & ugly: i need businesslogic to create an entity first, use the id and can then create a new ship or weapon

is there any other (better yet, THE) way to do this?

Improve this question

2 Answers 2

Reset to default
0

I don't think you have a database design problem here, you have an application design problem.

The logic you describe, of the form "I can only do X if I meet the following conditions ..." sounds to me like you need a state machine, and the data requirements for the state machine will be the driver behind the database schema.

There are quite a few educational resources on state machines, and I'd look for something Java=oriented.

Here's an interesting perspective: http://www.skorks.com/2011/09/why-developers-never-use-state-machines/

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Your problem is the classical problem of modeling inheritance in a relational database, and it is usually solved in two different ways. The first one is through the following tables:

Entities
  - id (primary key)
  - name

Ships
  - id (primary key) (foreign key for Entities)
  - speed

Weapons
  - id (primary key) (foreign key for Entities)
  - damage

Prerequisites
  - id1 (foreign key for Entities)
  - id2 (foreign key for Entities)
  (both id1 and id2 forming the primary key)

This is useful if you have other common attributes as well as specific attributes for Ships and Weapons.

The second one is through the use of two tables:

Entities
  - id (primary key)
  - name
  - kind (ship or weapon)
  - speed  (possibly null)
  - damage (possibly null)

Prerequisites
  - id1 (foreign key for Entities)
  - id2 (foreign key for Entities)
  (both id1 and id2 forming the primary key)

The choice of the solution depends on different factors, reading any good book on database modeling could help you in deciding, but in a case like this one I would prefer the first solution.

Improve this answer

1 Comment

maybe i don't understand you too well, but i think the first solution you present to me is the exact thing i proposed at the end of my question. So i think the problem is not solved, because for this solution to work, the application layer has to create an entry in the Table "Entities" first and use that id to create a new entry in the Tabe "Ships" or "Weapons" - which i think is bad design. Currently this is the way i am doing it, because i don't have a clever alternative. Solution 2 might work, but as you also stated, this is not preferred. Especially if the 2 Tables have much more columns?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.