Beginner Database architecture

Question

I am converting a spreadsheet to a database but how do i accommodate multiple values for a field?

This is a database tracking orders with factories. Import PO# is the unique key. sometimes 1 order will have 0,1,2,3,4 or more customers requiring that we place their price tickets on the product in the factory. every order is different. what's the proper way to accommodate multiple values in 1 field?

Answer 1

Generally, having multiple values in a field is bad database design. Maybe a one to many relationship will work in this scenario.

So you will have an Order table with PO# as the primary key, Then you will have a OrderDetails table with the PO# as a foriegn key. i.e. it will not be designated as a primary key.

For each row in the Order table you will have a unique PO# that will not repeat across rows.

In the OrderDetails table you will have a customer per row and because the PO# is not a primary key, it can repeat across rows. This will allow you to designate multiple customers per order. Therefore each row will have its own PriceTicketsOrdered field so you can know per customer what the price is.

Note that each customer can repeat across rows in the OrderDetails table as long as its for a different PO# and/or product.

This is the best I can tell you based on the clarity of your question.

Personally, I normally spend time desinging my database on paper or using some drawing software like visio before I start implementing my database in a specific software like MySql pr PostgreSql.

Reading up on ER Diagrams(Entity Relationship diagrams) might help you. You should also read up on Database normalization. Probably you should read up on database normalization first.

here is a link that might help:

http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561