Mysql – What should be the structure for a person-to-person store database

database-designMySQLPHP

I am going to create a person-to-person store with Laravel, a website whose users can share and sell their product or their mobile, TV, Computer, Shoes, Shirts, Books ,Cars, Bags, Animal etc.

I am going to create a table named Products. Other tables about product are, for example, Bugs, Animals, Cars, Shirts, Shoes or Computers.

products table:

product_id   |   cat_id     |   title    |   cost    |    timestamp

cars table:

      id     |  product_fk  |     brand   |   model   |    type    |  Fuel  | ...

Computers table:

     id     |  product_fk  |     brand   |   type     |    CPU     |  GPU    |  hard_disk  |  ...

I will create other, similar, tables.

I want to know if there is another way to do this which is better and easier?

What do you think about this structure?

Best Answer

There could be many way possible to store data for such scenario, as with your design attempt it seems that you are looking to have unique Id to identify each product and if that is the case the you are right with having a productstable and make that product_id an auto increment key.

Now the next thing to make sure that what ever the attributes of all these bugs, Animals, Cars, Shirts, Shoes, Computers which are common can be added in products table, attributes related to sales can be a good choice to save in this table.

Now Let say for car you will have different brand, design, Engine(Diesel/Petrol), seats, Color and so on you may need other look up tables to save these attributes similar for all the other product like Shoes, Computers etc.

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 UNIQUE (RoomType, Zone, Name), 
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.

Approach to Analyzing Database Requirements

You could have a base table to store the common "person" attributes, and then specialized tables for the more specific fields. Example:

Person
------
  id
  ref_num
  reg_dt
  addrs_line1
  addrs_line2
  postal_code
  phone_num

th_person
--------
  id
  person_id (FK to person.id)
  max_accepted_at_one_time

animals
-------
  id
  accepted_by_th_person (FK to th_person.id)

ph_person
---------
  id
  person_id (FK to person.id)
  animal_ref_num

cs_person
---------
  id
  person_id (FK to person.id)

donations
---------
  id
  date
  amount
  donor_id (FK to cs_person.id)

This might be a good starting point.

Best Answer

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Approach to Analyzing Database Requirements

Related Question