sql - User database design for marketplace-style app -

-

need best-practice wisdom i'm new database architecture. marketplace type app. main issue i'd advice on handling users.

what i'd achieve buyer , seller accounts share login/signup functionality , characteristics, seller accounts have ability sell, receive payments, etc. buyers can send requests, sellers can complete them. seller can buyer can do, additionally view , fulfill requests, , sell.

i'd go simple roles, except sellers have more complex relationships buyers in terms of products , payment info. sellers need able publicly listed, , have public profiles. i'm not sure having 1 big table both users types ideal this.

my current idea use polymorphic associations between base user table , seller table (potentially buyer specific table well):

user (buyer)

  • name
  • email
  • encrypted_password
  • (other authentication fields)
  • location
  • occupation
  • meta_id
  • meta_type

seller

  • name
  • location
  • occupation
  • etc

requests (belongs buyer , seller)

  • type
  • description
  • complete
  • buyer_id
  • seller_id

products (belongs seller)

  • description
  • category_id
  • seller_id

as can see, big issue fact buyers , sellers have duplicate data. reason because when i'm displaying sellers don't want have perform multi-table queries, maybe that's not issue? option have user base table , buyer , seller tables, they'd still contain duplicate info.

open possibilities.. what's best way go?

you can represent kind of relationship using database supertypes , subtypes.

for example, split data model 2 groups: users , roles. role can either buyer or seller, , user can have 0 or more roles.

i create following logical entities represent role relationship:

supertype

  • userrole (this name generic; suggest name better reflects role of both buyer , seller within application).

subtypes

  • buyer
  • seller

for physical design, suggest 1 of following designs:

  1. a single table contains columns supertype entity columns each of subtype entities. check constraints can used enforce not-null constraints subtype columns.
  2. one table supertype, separate table each subtype entity. columns common each subtype stored in supertype table, other columns stored in appropriate subtype table. type column added supertype table indicate type of entity. each subtype table includes foreign key relationship supertype table.
  3. hybrid approach combines aspects each of above designs.

access patterns

one factor consider when deciding how model subtype , supertype relationship whether queries need access columns both supertype , subtype tables. if of queries access columns supertype , subtype tables, single table may better design.

edit - suggest using first design, unless there compelling reason create separate tables subtypes. foreign keys include type column can used restrict relationships particular subtype.

mapping user role

to assign user role, can create many-to-many relationship between user table , supertype (userrole) table.


Comments

Post a Comment

Popular posts from this blog

c++ - OpenCV Error: Assertion failed <scn == 3 ::scn == 4> in unknown function, -

-
i read alot other solution still confused should mine... #include <opencv2/objdetect/objdetect.hpp> #include <opencv2/highgui/highgui.hpp> #include <opencv2/imgproc/imgproc.hpp> #include <iostream> #include <stdio.h> using namespace std; using namespace cv; int main(int argc, const char** argv) { //create cascade classifier object used face detection cascadeclassifier face_cascade; //use haarcascade_frontalface_alt.xml library face_cascade.load("haarcascade_frontalface_alt.xml"); //setup video capture device , link first capture device videocapture capturedevice; capturedevice.open(0); //setup image files used in capture process mat captureframe; mat grayscaleframe; //create window present results namedwindow("outputcapture", 1); //create loop capture , find faces while (true) { //capture new image frame capturedevice >> captureframe; ...
Read more

php - render data via PDO::FETCH_FUNC vs loop -

-
i've been using 2 methods render data. the first one: function name($id,$name){ return '<div id="'.$id.'">'.$name.'</div>'; } echo implode($pdo->query("select id,name user")->fetchall(pdo::fetch_func,'name')); the second one: $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); foreach($users $user){ echo name($user->id,$user->name); } i don't understand how pdo::fetch_func works. tried figure out. however, not well-documented. could please explain fetch mode? , also, 1 performs better? thank you. both methods wrong , have learn how use templates , how separate business logic presentation logic. $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); tpl::assign('users', $users); is code business logic part. then in template <?php foreach $users $row): ?> <div id="<?=$...
Read more

The canvas has been tainted by cross-origin data in chrome only -

-
uncaught securityerror: failed execute 'getimagedata' on 'canvasrenderingcontext2d': canvas has been tainted cross-origin data i getting above error in chrome. code(following) works fine in mozilla. have not corss domain issue. why above error in chrome? var wheel_canvas, wheel_context, can_w, can_h, wheel_grd; var large_radius = 160; var small_radius = 120; var wheel_canvas = document.getelementbyid('wheel_canvas'); var wheel_context = wheel_canvas.getcontext('2d'); var can_w = $(wheel_canvas).width(); var can_h = $(wheel_canvas).height(); var centerx = can_w / 2, centery = can_h / 2; var wheel_grd = null; test('#ff0000'); function test(hex) { $('#clrpicker').css({'left': 210, 'top': 210 }); inverted = hexbw(hex); $('#color_val_show').val(hex); current_color_hex_val_selected = hex; $('#color_val_show').css({'color':inverted,'background':hex}); fillgradient...
Read more