MySQL Forums
Forum List  »  General

Re: Parent child relationship questions
Posted by: Peter Brawley
Date: July 24, 2022 03:49PM

Basically, designing a database properly requires at least a semester of relational database knowledge. Before attempting a db design, you need to assimilate relational & design basics eg starting with docs like ...

https://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf

https://www.artfulsoftware.com/dbdesignbasics.html

... then working through some existing designs like the sakila and world databases available for MySQL.

If device_details is the parent and device_events is the child then, based on the slim info we have, you need something like ...

create table device_types (
  device_type_id smallint unsigned primary key,
  device_desc varchar(64)
);

create table device_event_types (
  device_event_type_id smallint unsigned primary key,
  device_event_type_desc varchar(64)
);

create table device_details (
  device_id int unsigned primary key,
  device_type smallint unsigned,
  foreign key(device_type) references device_types(device_type_id)
);
  
create table device_events (
  device_event_id int unsigned primary key,
  device_id int unsigned,
  foreign key(device_id) references device_details(device_id),
  device_event_type_id smallint unsigned,
  foreign key(device_event_type_id) references device_event_types(device_event_type_id)
);

... but you really need to walk through the entire specification as described in the 2nd link above.

Options: ReplyQuote


Subject
Written By
Posted
Re: Parent child relationship questions
July 24, 2022 03:49PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.