Desinging a database from scratch can be a daunting process.If you get it wrong it results in a lot of extra work. There are many options that are not necessarily wrong, but also create extra work downstream.
When you are starting from scratch the easiest way to get things going is to identify the entities pertaining to your database. An entity is most often a noun; a person, place or thing. Each entity needs to be distinct. Each row of data in an entity is unique, and may be uniquely defined by some key.
As you determine each entity, you can ask yourself, does this entity relate to any other entity in my database in any fashion. Does entity A have any relationship to entity B? If so, does A have one to many B? Does entity B have one to many A?
- If there are no relationships no action is necessary.
- If entity A has one to many B, then the key for entity A is a foreign key in Entity B.
- If entity B has one to many A, then the key for entity B is a foreign key in Entity A.
- If entity A has many B, and Entity B has many A, then you create a new table with keys from both Entity A and entity B. This new many to many relationship table becomes a new entity on its own, and the relationship test is performed on the new table as well.
So, don’t try and solve the whole thing all at once. Start with your first couple of entities, and keep moving forward, testing for relationships as each entity is determined.
Cheers,
Ben