0

I have this mockup for a database I will be creating. I'm wondering how I can further normalize it, and so far my thoughts are breaking out date into it's own table. What would be common practice?

Database

engineersmnky
  • 25,495
  • 2
  • 36
  • 52
Sam Lim
  • 197
  • 15
  • 2
    How do you intend to implement `start_date` and `end_date` as a link to `conditions.id`? Additionally I am not certain this question belongs on SO seems [DBA StackExchange](https://dba.stackexchange.com/) would be a better fit – engineersmnky Aug 18 '17 at 15:18
  • Seems an awfully lot like a homework problem. – bclarkreston Aug 18 '17 at 15:20
  • @engineersmnky Not a really a database administrator question, more like a database developer question. Both SO and DBA are fine IMO. – TT. Aug 18 '17 at 16:10
  • 1
    What normal form is "conditions" in now? If you "break out date into its own table", what normal form would "conditions" be in? – Mike Sherrill 'Cat Recall' Aug 18 '17 at 19:29
  • 1
    Normalization involves FDs & JDs. What are yours? Since you aren't giving them, you don't seem to know what normalization is. What is your reference for normlization? Why are you using that word? What *exactly* do you mean when you say you have done some normalization? What exactly do you want? What answer do you expect without giving a specification & tradeoffs that a design is supposed to satisfy? What reference are you using for information modeling & database design? PS What does "breaking out date into it's own table" mean? – philipxy Aug 19 '17 at 12:37

1 Answers1

0

The answer is: probably yes. But without having an exact definition of every field, i.e. what do they mean in the context of your data model, it's hard for us to give a good answer on this.

Looking at the trips table, I'm seeing the column zip_code which looks to be out of place. zip_code field is not directly related to the primary key of the trip table (AFAICT anyway). A zip code is a property of a city. I would say that zip_code should be stored in the city table.

What you are aiming for is probably to end up in a database normalized to the third normal form (3NF). You should read up on normalization and apply the rules up to 3NF. To go further into what this entails would be duplicating numerous tutorials, courses and books. You could take this question on SO as a starting point and try to apply this to your data model.

TT.
  • 15,774
  • 6
  • 47
  • 88