2 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

The process of data modeling is complex enough that you will need to go read some books about it to learn the details. The subject goes way beyond what can be covered here. However, to get you started, you can follow these (drastically oversimplified) steps:

Step 1: Make an Entity-Type for Each Tangible Thing

Start with making a logical data model. For each thing your system cares about (people, places, things, events) make an entity-type. For your system it might look like this:

enter image description here

Step 2: Identify Your Relationships

Look at your logical entity-types and determine how they relate to each other. Remember to focus on the relationships that matter to your system. Don't get bogged down looking at relationships that are possible or that are real, but which are not pertinent to your system.

enter image description here

Step 3: Transform Your Logical Model Into a Physical Model

Once you have a logical model that covers the objects and properties that are of interest to your system, you need to figure out how the logical model can be implemented using physical tables in your database.

This involves at least two very important steps:

  • Resolving many-to-many relationships into intersection tables
  • Normalizing your tables

In the case of your system, there are some many-to-many relationships that can be resolved. Normalization involves looking at table fields. Since we don't know what your fields are from your question, we can't really get into that topic here. Normalization is very important, however, and you should always make sure that you start from properly normalized tables and de-normalize only when you have very good, well considered, reasons to do so.

Your model with m:n relationships resolved would look like this:

enter image description here

Note that there isn't enough information in your question to be able to say for sure that this model is the start of what you need. I've had to make a number of assumptions about what your entities are and how they're related. This site isn't meant to be used for collaborative design in any case (see the FAQFAQ) - but I've tried to give you a quick outline of the process so you have someplace to start. If you get stuck with a specific design issue along the way, then you can definitely come back to ask for advice about it here on dba.se.

The process of data modeling is complex enough that you will need to go read some books about it to learn the details. The subject goes way beyond what can be covered here. However, to get you started, you can follow these (drastically oversimplified) steps:

Step 1: Make an Entity-Type for Each Tangible Thing

Start with making a logical data model. For each thing your system cares about (people, places, things, events) make an entity-type. For your system it might look like this:

enter image description here

Step 2: Identify Your Relationships

Look at your logical entity-types and determine how they relate to each other. Remember to focus on the relationships that matter to your system. Don't get bogged down looking at relationships that are possible or that are real, but which are not pertinent to your system.

enter image description here

Step 3: Transform Your Logical Model Into a Physical Model

Once you have a logical model that covers the objects and properties that are of interest to your system, you need to figure out how the logical model can be implemented using physical tables in your database.

This involves at least two very important steps:

  • Resolving many-to-many relationships into intersection tables
  • Normalizing your tables

In the case of your system, there are some many-to-many relationships that can be resolved. Normalization involves looking at table fields. Since we don't know what your fields are from your question, we can't really get into that topic here. Normalization is very important, however, and you should always make sure that you start from properly normalized tables and de-normalize only when you have very good, well considered, reasons to do so.

Your model with m:n relationships resolved would look like this:

enter image description here

Note that there isn't enough information in your question to be able to say for sure that this model is the start of what you need. I've had to make a number of assumptions about what your entities are and how they're related. This site isn't meant to be used for collaborative design in any case (see the FAQ) - but I've tried to give you a quick outline of the process so you have someplace to start. If you get stuck with a specific design issue along the way, then you can definitely come back to ask for advice about it here on dba.se.

The process of data modeling is complex enough that you will need to go read some books about it to learn the details. The subject goes way beyond what can be covered here. However, to get you started, you can follow these (drastically oversimplified) steps:

Step 1: Make an Entity-Type for Each Tangible Thing

Start with making a logical data model. For each thing your system cares about (people, places, things, events) make an entity-type. For your system it might look like this:

enter image description here

Step 2: Identify Your Relationships

Look at your logical entity-types and determine how they relate to each other. Remember to focus on the relationships that matter to your system. Don't get bogged down looking at relationships that are possible or that are real, but which are not pertinent to your system.

enter image description here

Step 3: Transform Your Logical Model Into a Physical Model

Once you have a logical model that covers the objects and properties that are of interest to your system, you need to figure out how the logical model can be implemented using physical tables in your database.

This involves at least two very important steps:

  • Resolving many-to-many relationships into intersection tables
  • Normalizing your tables

In the case of your system, there are some many-to-many relationships that can be resolved. Normalization involves looking at table fields. Since we don't know what your fields are from your question, we can't really get into that topic here. Normalization is very important, however, and you should always make sure that you start from properly normalized tables and de-normalize only when you have very good, well considered, reasons to do so.

Your model with m:n relationships resolved would look like this:

enter image description here

Note that there isn't enough information in your question to be able to say for sure that this model is the start of what you need. I've had to make a number of assumptions about what your entities are and how they're related. This site isn't meant to be used for collaborative design in any case (see the FAQ) - but I've tried to give you a quick outline of the process so you have someplace to start. If you get stuck with a specific design issue along the way, then you can definitely come back to ask for advice about it here on dba.se.

1
source | link

The process of data modeling is complex enough that you will need to go read some books about it to learn the details. The subject goes way beyond what can be covered here. However, to get you started, you can follow these (drastically oversimplified) steps:

Step 1: Make an Entity-Type for Each Tangible Thing

Start with making a logical data model. For each thing your system cares about (people, places, things, events) make an entity-type. For your system it might look like this:

enter image description here

Step 2: Identify Your Relationships

Look at your logical entity-types and determine how they relate to each other. Remember to focus on the relationships that matter to your system. Don't get bogged down looking at relationships that are possible or that are real, but which are not pertinent to your system.

enter image description here

Step 3: Transform Your Logical Model Into a Physical Model

Once you have a logical model that covers the objects and properties that are of interest to your system, you need to figure out how the logical model can be implemented using physical tables in your database.

This involves at least two very important steps:

  • Resolving many-to-many relationships into intersection tables
  • Normalizing your tables

In the case of your system, there are some many-to-many relationships that can be resolved. Normalization involves looking at table fields. Since we don't know what your fields are from your question, we can't really get into that topic here. Normalization is very important, however, and you should always make sure that you start from properly normalized tables and de-normalize only when you have very good, well considered, reasons to do so.

Your model with m:n relationships resolved would look like this:

enter image description here

Note that there isn't enough information in your question to be able to say for sure that this model is the start of what you need. I've had to make a number of assumptions about what your entities are and how they're related. This site isn't meant to be used for collaborative design in any case (see the FAQ) - but I've tried to give you a quick outline of the process so you have someplace to start. If you get stuck with a specific design issue along the way, then you can definitely come back to ask for advice about it here on dba.se.