I'm bit confused on creating a good relational database. I'm using phpmyAdmin to produce a database. I've the next four tables. Don't be concerned about this fact place and cost are optional they simply are.

  1. Person (Mandatory)
  2. Item (Mandatory)
  3. Place (Optional)
  4. Cost (Optional)

Item may be the primary table. It'll always have person linked. * I understand you need to do joins in mysql for that tables. If I wish to link the tables together I possibly could use composite secrets (while using ids from each table), however is probably the most right way to link the tables? Additionally, it means item may have 5 ids including its very own. All of this cause null values (apparently a large no no, that we can understand) if place and cost are optional and therefore are not utilized on one admission to the products table I'll possess a null value there. Help!

Thanks ahead of time. I really hope this will make sense.

NULL values

Additionally, it means item may have 5 ids including its very own. All of this cause null values (apparently a large no no, that we can understand) if place and cost are optional and therefore are not utilized on one admission to the products table I'll possess a null value there

I'm this really is one situation where NULL values are great, and that i certainly wouldn't have doubts about putting this into my database design.

One of the ways I have seen others attain the same factor without NULL values is to produce a record within the optional tables (place and cost inside your example) by having an ID of that signifies there is no related record - but this just makes 10 occasions more work with the developer from the application to filter this info out - it is simpler to perform a join and when you do not get any records back, you will find no related records within the optional table.

Just be sure you perform a LEFT or RIGHT OUTER join if you wish to return Items no matter when they have been a Place or Price connected (you will get NULL values within the optional table posts for Items that do not have connected records) as well as an INNER join should you only want the Items that do come with an connected optional record.

Composite Secrets

An amalgamated secret is a type in a table that's comprised of several column. If all of your Person, Item, Place and Price all come with an ID (even when it is simply a car-incrementing number) you will not require a composite key - only a primary key column in every table, along with a foreign key column within the Item table for every related table - e.g. item_id, person_id, place_id, price_id. You condition that Item features its own ID, which means you should not require a composite key - only a primary key around the item_id column.