Search code examples
phpmysqlsqldatabase-designnormalization

Database design (normalization?)

In the following situation, what would the database design look like?

This is for some sort of inventory system.

I want users to be able to create a item-type (lets say, a Laptop). I also want users to be able to say the serial number and MAC address must be unique. This part confuses me where to check for unique values, since I have no idea how to make a table with all items in it, with unique values..

Let's say a user creates another item-type that has no serial number or any unique fields, this means I can't build my DB with property1 till property10 fields in the database.

I also don't want to build a table for every item type, since that would involve too advanced table management in PHP.

Any suggestions on how to build this DB?

Solution

  • Just to clarify I understand your requirements correctly, you meant to create a table that unique rule only applies to a subset of the table instead of the entire table?

    If so, I think there will be two options

    • have two tables, one with unique rules and one without OR
    • Enforce the unique rules in application level as business rules instead of database level.